SQL

QUERIES
SQL commands are used to create, manage, retrieve, and control data in a database.
They are divided into 5 main categories:

•DDL → Data Definition Language •DDL → Design the table

•DQL → Data Query Language •DQL → View the data
•DML → Data Manipulation Language •DML → Change the data
•DCL → Data Control Language •DCL → Control access
•TCL → Transaction Control Language •TCL → Save or undo work
 Step 1: DDL (Create Table)

 CREATE TABLE Student (

ID INT PRIMARY KEY,
Name VARCHAR(50),
Marks INT
);
 Step 2: DML (Insert Data)

 INSERT INTO Student (ID, Name, Marks)

VALUES (1, 'Pradeep', 85),
(2, 'Anita', 90);
 Step 3: DQL (Retrieve Data)

 SELECT * FROM Student;

 Step 4: DML (Update Data)

 UPDATE Student
SET Marks = 95
WHERE ID = 1;
 Step 5: TCL (Transaction Control in MySQL)

 START TRANSACTION;

 DELETE FROM Student WHERE ID = 2;

 ROLLBACK; -- Undo delete

 COMMIT; -- Save final changes

 Step 6: DCL (User Permission – MySQL)

 CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass123’;

 GRANT SELECT ON Student TO 'user1'@'localhost’;

 REVOKE SELECT ON Student FROM 'user1'@'localhost';

 1. DDL (Data Definition Language)
 CREATE
 CREATE TABLE Student (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Marks INT
);
 ALTER
 ALTER TABLE Student ADD Age INT;
 DROP
 DROP TABLE Student;
 TRUNCATE
 TRUNCATE TABLE Student;
 2. DML (Data Manipulation Language)
 INSERT
 INSERT INTO Student (ID, Name, Marks, Age)
VALUES (1, 'Pradeep', 85, 21);
 UPDATE
 UPDATE Student
SET Marks = 90
WHERE ID = 1;
 DELETE
 DELETE FROM Student
WHERE ID = 1;
 3. DQL (Data Query Language)
 SELECT (Basic)
 SELECT * FROM Student;
 SELECT with WHERE
 SELECT Name FROM Student
WHERE Marks > 80;
 4. TCL (Transaction Control Language)
 START TRANSACTION
 START TRANSACTION;
 SAVEPOINT
 SAVEPOINT sp1;
 ROLLBACK
 ROLLBACK TO sp1;
 COMMIT
 COMMIT;
 TCL Simple Example (MySQL)
 Step 1: Start Transaction
 START TRANSACTION;

 Step 2: Insert Data

 INSERT INTO Student VALUES (1, 'Pradeep', 80);

Step 3: Create Savepoint

 SAVEPOINT sp1;

 Step 4: Insert Another Record

 INSERT INTO Student VALUES (2, 'Anita', 90);

 Step 5: Rollback to Savepoint

 ROLLBACK TO sp1;
 This will remove Anita's record but keep Pradeep's record.

 Step 6: Commit Changes

 COMMIT;
 5. DCL (Data Control Language)
 CREATE USER
 CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass123';
 GRANT
 GRANT SELECT, INSERT ON Student TO 'user1'@'localhost';
 REVOKE
 REVOKE INSERT ON Student FROM 'user1'@'localhost';
 SQL QUERY “CREATE”

There are two CREATE statements available in SQL:

1. SYNTAX TO
CREATE
DATABASE:

Create database
database_name;

[Link] TO
CREATE TABLE:

table_name (
column1
data_type(size),
column2
data_type(size),
column3
data_type(size),
);
SQL INSERT STATEMENT

1. In case of adding values for all the columns of the table, no need to specify the column names in the SQL query.
The order of the values should be in the same order as the columns in the table.

INSERT INTO Customers (CustomerName, CustomerAge, Area, City, PostalCode, Country)

VALUES (‘John', ’28’, ’Whitefield', ’Bangalore', ’560067’, ’India’);
2. In case of adding values in few columns, specify both the column names and the values to be inserted:

INSERT INTO Customers (CustomerName, City, Country)

VALUES (‘Ramya’, ’Chennai', ’India');
SELECT as SQL query

The select statement is probably the most used SQL command. It is used for retrieving rows from the
database and enables the selection of one or many rows or columns from tables in the database.
The Doctor table has following columns:
mysql> select d_name from doctor;

mysql> select * from doctor; mysql> select d_id, d_name from doctor;

mysql> select d_id from doctor; mysql> select avg(salary) from doctor;
FROM QUERY
’ From’ is used to specify which table to select or delete data from.

Problem : To select data from a

table

Solution :
mysql> select * from employee;

Problem : To select employee name

and designation from a table

Solution :
mysql> select e_name,
e_designation from employee;
Problem : To delete data where salary is
less than 45000 from a table

Solution :
mysql> delete from employee where
e_salary<45000;
ALTER - SQL QUERY

• To add, delete, or modify columns in an existing table

• To add and drop various constraints on an existing table
Alter-Add Alter-Modify

Alter- Drop
 DISTINCT AS SQL QUERY

When only one column (expression) is provided

in the DISTINCT clause, the query will return the
This keyword returns only distinct values in unique values for that column.
the specified column.
The table Company has following columns

When more than one column (expression) is provided in

the DISTINCT clause, the query will retrieve the unique
combinations for the columns listed.
UPDATE Query

The SQL UPDATE Query is used to modify the existing records in a table.
You can use the WHERE clause with the UPDATE query to update the
selected rows, otherwise all the rows would be affected.

Syntax
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

Example: Consider the TEACHER table having the following records

SQL QUERY
UPDATE
DELETE – SQL QUERY

▪ Delete statement is used to delete the rows in a table.

▪ SYNTAX: Delete from ‘table name’ where ‘column name=some value’

▪ The where clause is used to specify the record or row that should be deleted.
▪ Delete all rows: All rows can be deleted without deleting the table.

▪ Syntax: Delete from ’table name’

TRUNCATE AS SQL QUERY

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself. It is similar to DELETE
command with no WHERE clause.

EXAMPLE:
The Student table contains the following data: Inserting new values:

TRUNCATE TABLE table_name;

SQL QUERY AS ‘where’
SQL QUERY AS ‘OR’
SQL QUERY-
“ORDER”
ASC

DESC
 mysql> create datbase PHARMACY_BALAJI_PHARMA;
mysql> use pharmacy_balaji;
BETWEEN SQL mysql> create table DRUGS(dg_id int(5),dg_RxNorms_code
QUERY varchar(20),dg_Manufacturer varchar(20),dg_form varchar(20),dg_Billamount
float(20),dg_Name varchar(50));
Problem:List all the drugs name where the billing amount between 120 to 170?
Solution:
mysql> select dg_Name from drugs where dg_Billamount between 120 and 170;
LIKE Query in SQL The LIKE command is used in a WHERE clause to
search for a specified pattern in a column.

You can use two wildcards with LIKE:

% - Represents zero, one, or multiple characters
_ - Represents a single character

SELECT FROM table_name

WHERE column LIKE 'XXXX%'

or

SELECT FROM table_name

WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name

WHERE column LIKE 'XXXX_’

or

SELECT FROM table_name

WHERE column LIKE '_XXXX'

or

SELECT FROM table_name

WHERE column LIKE '_XXXX_'
Examples:
 SQL JOINS
SELECT Table1.Column1, Table1.Column2, Table2.Column1…..
FROM Table1
JOIN
ON [Link] = [Link];

• Commands which are used to combine

rows from 2 or more tables BASED ON A
RELATED COLUMN between those tables.

• Predominantly used when we need to

extract data from tables having:

One-to-many or

Many-to-many relationships.
mysql> USE example;
Database changed
mysql> SHOW tables;
Empty set (0.00 sec)

CREATE table Employees(e_id int(2), e_name varchar(20),e_city varchar(30));

Query OK, 0 rows affected (0.14 sec)

CREATE table Projects(p_id int(3),e_id int(2),p_name varchar(20));

Query OK, 0 rows affected (0.14 sec)
 LEFT JOIN

RIGHT JOIN
FULL JOIN
or
UNION
  From the following table, write a SQL query to count
SQL QUERY AS the number available rooms. Return count as
'COUNT' "Number of available rooms"
“LIMIT” QUERY in SQL
• Used to specify the number of
records to return
• Syntax:
SELECT column_name(s) FROM table

LIMIT [offset,] row_count;

• Follows zero-based array indexing

AND Operator

 The MySQL AND condition can be

combined in a SELECT, UPDATE, or
DELETE statements.

 When combining these conditions, it is

important to use parentheses so that the
database knows what order to evaluate
each condition.
SUM() FUNCTION QUERY
➢ Create a “table” in databases
➢ Insert values into the rows
➢ Then if want to sum the values in the columns
• Query to be used:
select sum(“column name”) from “table”;

Eg: select sum(Pro_Qty) from Products;

Sum(Pro_Qty)
189
 SQL KEYWORD-DROP
• Removes a component from relational database management system
• Drop-Table: syntax drop table table_name;

• Drop-Database: syntax drop database database_name;

 SQL QUERY “AS”
• The keyword AS is used as an alias to rename the column or table.
• SELECT COLUMN_NAME AS ALIAS_NAME FROM TABLE_NAME;
• SELECT COLUMN_NAME FROM TABLE_NAME AS ALIAS_NAME;
 Query: ‘IS NULL’ & ‘IS NOT NULL’
• If a field in a table is optional, it is
possible to insert a new record or
update a record without adding a value
to this field. Such a field is saved with a
null value.
• A null value is different from a zero
value or a field that contains spaces.
Used to test for empty (NULL) values

Used to test for non-empty (NOT NULL) values

 SQL CASE The CASE statement goes through conditions and returns a value when
the first condition is met. So, once a condition is true, it will stop
STATEMENT reading and return the result. If no conditions are true, it returns the
value in the ELSE clause.

SELECT s_id,
s_firstname,s_lastname,s_marks,
CASE
WHEN s_marks >=45 and Student_Details table
s_marks<=100 then 'Passed'
ELSE 'Failed'
END AS s_result
FROM Student_Details;
SELECT s_id, s_firstname,s_lastname,s_marks,
CASE
WHEN s_marks >= 90 THEN 'Outstanding'
WHEN s_marks >= 75 AND s_marks < 90 THEN 'Excellent'
WHEN s_marks >= 65 AND s_marks < 75 THEN 'Good' Student_Details table
WHEN s_marks >= 55 AND s_marks < 65 THEN 'Average'
WHEN s_marks >= 50 AND s_marks < 60 THEN 'Below
Average'
WHEN s_marks < 40 THEN 'Failed'
END AS s_result
FROM Student_Details; x
 • The SQL statement COMMIT causes a transaction to
complete.
SQL QUERY
• COMMIT command to make changes permanent
COMMIT save to a database during the current transaction.