SQL [Structured Query Language]
I. DDL[DATA DEFINITION LANGUAGE]
1. Create Database Command
Syntax:
CREATE DATABASE database_name;
Ex:
CREATE DATABASE College;
Database College is created successfully.
Verification:
SHOW DATABASES;
1.1. Use Database Command
Syntax:
USE database_name;
Ex:
USE College;
2. Create Table Command
Syntax:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype);
EX:
create table cust(cname varchar(10),city varchar(10),pname varchar(10),
quantity int, price numeric(5,2),orderdate date);
3. DESC
Syntax:
DESC table_name;
Ex:
desc cust;
4. Alter Table Command
4.1 ADD
ALTER TABLE table_name ADD column_name datatype;
Ex:
ALTER TABLE cust ADD email VARCHAR(10);
4.2 MODIFY
ALTER TABLE table_name MODIFY column_name datatype;
Ex:
ALTER TABLE cust MODIFY email VARCHAR (100);
4.3 DROP
ALTER TABLE table_name DROP COLUMN column_name;
Ex:
ALTER TABLE cust DROP COLUMN email;
5. Drop Table Command
DROP TABLE table_name;
Ex:
DROP TABLE Cust;
6. Drop Database Command
DROP DATABASE database_name;
Ex:
DROP DATABASE College;
I.I. TABLE CREATION WITH CONSTRAINTS
1. NOT NULL
Syntax:
column_name datatype NOT NULL
Ex:
CREATE TABLE Employees ( employee_id INT NOT NULL, first_name
VARCHAR(50) NOT NULL, last_name VARCHAR(50) );
2. UNIQUE
Syntax
column_name datatype UNIQUE
Ex:
CREATE TABLE Employees ( employee_id INT UNIQUE, email VARCHAR(100)
UNIQUE );
3. CHECK
Syntax:
column_name datatype CHECK (condition)
Ex;
CREATE TABLE Employees ( employee_id INT PRIMARY KEY, first_name
VARCHAR(50), last_name VARCHAR(50), age INT CHECK (age >= 18) );
4. DEFAULT
Syntax:
column_name datatype DEFAULT default_value
Ex:
CREATE TABLE Employees ( employee_id INT PRIMARY KEY, first_name
VARCHAR(50), last_name VARCHAR(50), hire_date DATE DEFAULT
CURRENT_DATE );
5. AUTO_INCREMENT
Syntax:
column_name INT AUTO_INCREMENT
Ex:
CREATE TABLE Employees ( employee_id INT AUTO_INCREMENT PRIMARY
KEY, first_name VARCHAR(50), last_name VARCHAR(50) );
6. PRIMARY KEY
Syntax:
CREATE TABLE table_name(
column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype ,
columnN datatype ));
EX:
create table cust(cname varchar(10) PRIMARY KEY,city varchar(10),pname
varchar(10),quantity int, price numeric(5,2),orderdate date);
7. FOREIGN KEY
Syntax:
CREATE TABLE table_name(
column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype REFERENCES table_name(column name));
EX:
create table orders(odno int primary key, cname varchar(10) references
cust(cname),pname varchar(10));
II. DML [Data Manipulation Language]
1. INSERT INTO STATEMENT
Syntax 1:
INSERT INTO TABLE_NAME (column1, column2, column3,
...columnN) VALUES (value1, value2, value3,...valueN);
EX:
insert into cust values('anu','EKM','sprite',10,100.00,'16-jun-21');
insert into cust values('bibi','KTM','lays',6,30.00,'16-jun-21');
insert into cust values('chinnu','KTM','sprite',4,100.00,'15-jun-21');
2. DELETE
Syntax 1:
DELETE FROM table_name WHERE condition;
EX: delete from cust where quantity<5;
Syntax 2: [To Delete All Records]
DELETE FROM TABLE_NAME;
Ex: Delete from cust;
3. UPDATE
Syntax1:
UPDATE table_name SET column1 = value1, column2 =
value2...., columnN = valueN WHERE [condition];
Ex:
update cust set name=‘SBK’ where cid=2;
Syntax 2: [To update All Values]
UPDATE table_name set column=value;
Ex:
update cust set odate=’23-05-2021’;
4. SELECT
Syntax:
1. SELECT * FROM table_name;
2. SELECT COLUMN1,COLUMN3 FROM table_name;
3. SELECT * FROM table_name where COLUMN1=VALUE1;
4. SELECT COLUMN1 FROM table_name where COLUMN1=
VALUE1;
EX:
select * from cust;
select pname,price,quantity from cust where cname='anu';
select * from cust where cname='bibi';
select * from cust where price<50.00;
II.I. OPERATORS
1. AND
select * from TABLE_NAME where COLUMN1=’VALUE1’ AND
COLUMN2=VALUE2;
select * from cust where pname='sprite' and quantity<5;
2. OR
select * from TABLE_NAME where COLUMN1=’VALUE1’ OR
COLUMN2=VALUE2;
select * from cust where pname='sprite' or quantity<5;
3. BETWEEN
Select * from TABLE_NAME where COLUMN1 BETWEEN RANGE
VALUES;
select * from cust where quantity between 5 and 10;
4. NOT operator
select * from TABLE_NAME where NOT COLUMN1=’VALUE1’;
select * from cust where not city='EKM';
5. LIKE operator
select * from TABLE_NAME where COLUMN1 LIKE ‘V%’ OR ‘_1’ ;
select * from cust where city like 'K%';
select * from cust where city like '%M';
select * from cust where city like '_%M';
select * from cust where cname like '%y';
6. ORDER BY..ASC/DESC
• SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME
ASC;
• SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME
DESC;
EX:
select * from cust order by cname asc;
select * from cust order by cname desc;
select * from cust order by pname asc;
select * from cust order by price asc;
7. IN operator
Select * from TABLE_NAME where COLUMN_NAME
IN(‘VALUE1’,’VALUE2’);
Ex:
select * from cust where city in('EKM','ALPY');
select * from cust where not city in('EKM','ALPY');
8. DISTINCT
• SELECT DISTINCT column1, column2,..., columnN FROM
table_name;
Ex:
select distinct city from cust;
select distinct * from cust;
II.II. AGGREGATE FUNCTIONS
COUNT() function
SELECT COUNT(*) AS TOT_CUST FROM CUSTOMER;
SUM() functions
SELECT SUM(PRICE) AS TOT_PR FROM CUSTOMER;
AVG() function
SELECT AVG(quantity) AS AVG_QA FROM CUSTOMER;
MIN() function
SELECT MIN(price) AS MIN_SAL FROM EMPLOYEE;
MAX() function
SELECT MAX(price) AS MAX_SAL FROM EMPLOYEE;
GROUP BY AND HAVING CLAUSE
GROUP BY
• The GROUP BY statement groups rows that have the same values into
summary rows.
Ex: SELECT PNAME, SUM(PRICE) FROM CUSTOMER GROUP
BY Pname;
HAVING clause
• The HAVING Clause is used to specify conditions that filter which
group results appear in the results.
Ex: SELECT PNAME, SUM(PRICE) FROM CUSTOMER GROUP
BY pname WHERE SUM(PRICE)>200;
II. Nested Queries
Select cus_id,cname from customer where cus_id in(select cus_id from
purchase where pname=‘lays’);
III. JOIN
select * from customer ,product;
Select * from customer, product where customer.cid=product.cid and
pname=‘lays’;
DCL [DATA CONTROL LANGUAGE]
1. GRANT Command
GRANT privilege_type ON object TO user [WITH GRANT OPTION];
EX:
GRANT SELECT ON Employees TO 'john_doe'@'localhost';
GRANT ALL PRIVILEGES ON Employees TO 'admin'@'localhost';
2. REVOKE Command
REVOKE privilege_type ON object FROM user;
REVOKE ALL PRIVILEGES ON Employees FROM 'manager'@'localhost';
LAB EXCERSISES
1. LIST 1
2. LIST 2
3. LIST 3
4. LIST 4
5. LIST 5
