Structured Query Language (SQL) (CH-12)

SQL:

 It is a Language that is used to create, modify and access and

managing data in relational databases.
Advantages of SQL:
 It is very easy to learn and use.
 Large volume of databases can be handled quite easily.
 It is not a case-sensitive language.
 It can also be used to create, insert, delete and control access to the
data in database.
MYSQL:- is an open source and freely available Relational Database
Management System (RDBMS) that uses SQL. It is a Language that is
used to create, modify and access and managing data in relational
databases.
Download from www.mysql.org.
Classification of SQL Statements:
In a database you can define the structure of the data and manipulate the
data using some commands. There are two types of languages for this
task. These are:
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
Data Definition Language (DDL)
A data definition language or data description language (DDL) is a
standard for commands that define the different structures in a database.
DDL statements create, modify, and remove database objects such as
tables, indexes, and users. Common DDL statements are CREATE,
ALTER, and DROP.
Data Manipulation Language (DML)
A data manipulation language (DML) is a language that enables users to
access and manipulate(change) data in a database. The goal is to provide
efficient human interaction with the system. Data manipulation involves:
• Retrieval of information from the database- SELECT statement
• Insertion of new information into the database - INSERT statement
• Deletion of information in the database - DELETE statement
• Modification of information in the database - UPDATE statement

There are two types of DML:

• Procedural: The user specifies what data is needed and how to get it
• Nonprocedural: The user only specifies what data is needed. This is
easier for the user but may not generate code as efficient as that
produced by procedural languages.

Data types: means to identify type of data type and associated features
and functions.
Datatype Syntax Description
INTERGER INTERGER It stores whole number. The range
OR int is -2,147,483,648 to 2,147,483,647
OR integer
DECIMAL DECIMAL(SIZE,PR Stores in DECIMAL format
ECISION) e.g DECIMAL(8,2)
5 digit before decimal , 2 digit after
decimal and 1 digit place the
decimal point
CHARACTER CHAR(SIZE) It stores a maximum of 0 to 255
(FIXED LENGTH) characters
CHARACTER VARCHAR(SIZE) It stores up to 65,535 characters.
(VARIABLE OR
LENGTH) VARCHAR2(SIZE)
DATE DATE Store date in „yyyy/mm/dd‟ format
TIME TIME Store time in hh:mm:ss format
BOOLEAN BOOLEAN Used for storing logical values,
(LOGICAL) either true or false
MEMO/LONG MEMO OR LONG It allows storing characters or
remarks up to 2 GB per record.

Constraints in SQL:
 Constraints are the conditions that can be enforced on the attribute
of a relation.
 It is a rule associated with the tables.
The various constraints available in SQL are:
1. NOT NULL 4. UNIQUE
2. PRIMARY KEY 5. DEFAULT
3. CHECK 6. FOREIGN KEY

SQL Commands:
CREATE DATABASE: This command is used to create a database in
RDBMS.
Syntax: CREATE DATABASE <DATABASE_NAME>;
Eg. CREATE DATABASE School;

Opening Database:
Syntax: USE <database_name>
Eg. USE School;
CREATE TABLE:
Create statement is used for creating a database or a table in any
RDBMS Software. A commonly used CREATE command is the
CREATE TABLE command. The general syntax of the create statement
is shown below.

CREATE TABLE <TABLENAME>

(<column_name1> <Date type> (size) Constraints)
(<column_name2> <Date type> (size) Constraints)
(<column_name3> <Date type> (size) Constraints)…..);

For example,
CREATE TABLE XII
(Adm_No INTEGER primary key,
Name VARCHAR(50) not null,
Stream VARCHAR(50),
Marks integer CHECK>=40,
Contact_Number INTEGER);
Viewing a Table Structure:- To view a table structure, DESCRIBE OR
DESC command is used.
Syntax: DESCRIBE <TABLENAME>;
DESC <TABLE NAME>;
Eg. DESC XII;

INSERT statement
INSERT statement is used to add one or more records to a database.
The general syntax of the insert statement is shown below.
INSERT INTO <table_name>
VALUES
<value1, value2, value3 ...>;
OR
INSERT INTO <table_name>
<column1, column2, column3...>
VALUES
<value1, value2, value3 ...>;
To add a record in the database created earlier, type the following and
click Execute.
insert into XII
values („1‟, “Ranjith Singh”, „SCIENCE‟ ,‟67‟, „32435363‟);

insert into XII (“AdmNo”, “Name”, “Stream”, “Marks”, “Contact”)

values(„2‟, “Harshal chandna”, „Arts‟, „89‟, „23223444‟);

UPDATE statement (Modifying data of a table)

Update statement is used for modifying records in a database. The
general syntax of the update
statement is as follows:
UPDATE <table_name>
SET <column_name> = value [, column_name = value ...]
[WHERE<condition>]
To update a record using update statement, type the following and click
Execute.
Update XII
set Name = „Harshal Chawla‟
where Rollno = 2;

DELETE statement
Delete Statement is used to remove one or more records in a database.
The general syntax of the
delete statement is as follows:
DELETE FROM <table_name> [WHERE] <condition>;
To delete one of the records in the table created earlier using delete
statement, type the following
and click Execute:
delete from SDetails where ID=8;
delete from ClassXC where Admno=2123;

TRUNCATE STATEMENT
Used to delete all the rows from the table and free the space containing
the table.
Syntax: TRUNCATE TABLE <TABLENAME>;
Eg. TRUNCATE TABLE XII;

DROP STATEMENT:
Use to delete a database or table permanently.
Syntax:- DROP DATABASE <DATABASE NAME>
DROP TABLE <TABLE NAME>
Eg. DROP DATABASE School;
DROP TABLE XII;

ALTER STATEMENT
Used to modifying the definition of a column in a table.
 To add new column
 To rename any existing column
 To change data type
 To delete a column
Syntax:- ALTER TABLE<table_name>
ADD (<column_name> <data_type> <size> <constraints>);

ALTER TABLE "table_name"

Change "column 1" "column 2" ["Data Type"];
ALTER TABLE <table_name>
DROP <column_name>;
The basic syntax of an ALTER TABLE command to change the DATA
TYPE of a column in a table is as follows.
ALTER TABLE table_name
MODIFY column_name datatype;

SELECT STATEMENT:-
A SELECT statement retrieves zero or more rows from one or more
database tables or database views. In most applications, SELECT is the
most commonly used Data Manipulation Language (DML) command.

1. Selection:-

Select <column_1>,<column_2> From <table_name>;

To retrieve all the columns from a table:

SELECT * FROM <TABLENAME>;
SELECT * FROM XII
2. Using WHERE clause: WHERE marks>97;

SELECT * FROM <Table_name>

WHERE <condition>; SELECT * FROM XII
WHERE marks>97 and gender=‟M‟;
SELECT * FROM <Table_name>
WHERE <condition1> AND <condition2>;
SELECT * FROM XII
WHERE marks>97 OR gender=‟M‟;
SELECT * FROM <Table_name>
WHERE <condition1> OR <condition2>;
Condition 1 Condition 2 Result (AND) Result(OR)
True True True True
True False False True
False True False True
False False False False

3. DISTINCT clause: Use to remove duplicate rows from the results

of a SELECT statement.
SELECT DISTINCT<col_name> FROM <table_name>
SELECT DISTINCT stream FROM XII;

Sorting in SQL:
4. ORDER BY clause: used to sort the data in ascending or
descending order.
SELECT <col_name> FROM<table_name>
WHERE <condition> #optional
ORDER BY <col_name> ASC/DESC;
SELECT * FROM XII ORDER BY marks;

5. GROUP BY clause: used in SELECT statement to collect data

across multiple records and group the results on the basis of one or
more columns.
SELECT <col_name>,<col_name>,<aggregate_function(*)>
FROM<table_name>
WHERE <condition>
GROUP BY <col_name>
SELECT name,marks, stream, count(*)
FROM XII
WHERE marks>97
GROUP BY stream;
6. HAVING clause: is used in combination with the GROUP BY
clause. It is used to filter the records by specifying a condition,
which a GROUP BY returns.
SELECT <col_name1>,<col_name1>,<aggregate_function(*)>
FROM<table_name> SELECT stream, sum(marks)
WHERE <condition> FROM XII
GROUP BY <col_name> GROUP BY stream
HAVING <condition> HAVING sum(marks) >130;

SQL Special Operators:

7. Using BETWEEN …. AND: defines a range of values. The range
includes both the lower and upper values. The values can be
numbers, text, or dates.
SELECT <col_name>,<col_name> ,…FROM<table_name>
WHERE <col_name> BETWEEN <value1> AND <value2>
8. Using NOT BETWEEN…..AND
SELECT <col_name>,<col_name> ,…FROM<table_name>
WHERE <col_name>NOT BETWEEN <value1> AND
<value2>

9. Using IN: This operator select values that match any value in the
given list.
SELECT <col_name>,<col_name> ,…FROM<table_name>
WHERE <col_name> IN <value1,value2,…>

10. Using NOT IN: It works opposite to IN operator.

SELECT <col_name>,<col_name> ,…FROM<table_name>
WHERE <col_name> NOT IN <value1,value2,…>

Aggregate functions in SQL: Some of the commonly used

mathematical functions are SUM( ), AVG( ), COUNT( ), MIN( ),
MAX( ),etc.
a. SUM( ):
SELECT sum(marks) FROM XII;
b. AVG( ):
SELECT avg(marks) FROM XII;
c. MAX( ):
SELECT max(marks) FROM XII;
d. MIN( ):
SELECT min(marks) FROM XII;
e. COUNT( ):
SELECT count(col_name/*) FROM <table_name>;
SELECT count(marks) FROM XII;
SELECT count(*) FROM XII;

f. COUNT DISTINCT:
SELECT COUNT(DISTINCT col_name) FROM <tab_name>;
SELECT COUNT(DISTINCT stream) FROM XII;

SQL JOINS: is used to combine rows from two or more tables,

based on a common field between them.
Syntax:
SELECT <col_no1><col_no2> FROM <table1>,<table2>

1. Equi Join
2. Natural Join

A. Equi Join: It is a simple SQL join condition that uses equal sign(=)
as a comparison operator for defining a relationship between two
tables on the basis of a common field.
Syntax:
SELECT <col_no1><col_no2> FROM <table1>,<table2>
WHERE <table1.colname>=<table2.colname>
B. Natural Join: The SQL NATURAL JOIN is a type of EQUI JOIN and is
structured in such a way that, columns with the same name of associated
tables will appear once only.
Syntax:
SELECT * FROM table1 NATURAL JOIN table2;
Sample table: foods
| ITEM_ID | ITEM_NAME | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1 | Chex Mix | Pcs | 16 |
| 6 | Cheez-It | Pcs | 15 |
| 2 | BN Biscuit | Pcs | 15 |
| 3 | Mighty Munch | Pcs | 17 |
| 4 | Pot Rice | Pcs | 15 |
| 5 | Jaffa Cakes | Pcs | 18 |
| 7 | Salt n Shake | Pcs | |
+---------+--------------+-----------+------------+
Sample table: company
| COMPANY_ID | COMPANY_NAME | COMPANY_CITY |
+------------+---------------+--------------+
| 18 | Order All | Boston |
| 15 | Jack Hill Ltd | London |
| 16 | Akas Foods | Delhi |
| 17 | Foodies. | London |
| 19 | sip-n-Bite. | New York |
+---------+--------------+-----------+------+

To get all the unique columns from foods and company tables, the following SQL
statement can be used:

SQL Code:

SELECT *

FROM foods

NATURAL JOIN company;

Output:
COMPANY_ID ITEM_ID ITEM_NAME ITEM_UNIT COMPANY_NAME COMPANY_CITY
---------- ---------- ------------------------- ---------- -----
16 1 Chex Mix Pcs Akas Foods Delhi
15 6 Cheez-It Pcs Jack Hill Ltd London
15 2 BN Biscuit Pcs Jack Hill Ltd London
17 3 Mighty Munch Pcs Foodies. London
15 4 Pot Rice Pcs Jack Hill Ltd London
18 5 Jaffa Cakes Pcs Order All Boston