DATABASE MANAGEMENT SYSTEM – DAY 06
SQL TRUNCATE Statement
The TRUNCATE statement is used to delete existing records in a table except the table.
TRUNCATE TABLE table_name;
EX:
TRUNCATE TABLE CustomerDetails;
SQL COUNT Statement
The COUNT statement is used to display number of existing records in a table.
SELECT COUNT (field_name) FROM table_name;
EX:
SELECT COUNT (name) FROM CustomerDetails;
SQL AUTO_INCREMENT
EX:
INSERT INTO Student VALUES(1,'Siripala','Matara');
INSERT INTO Student (name,address)VALUES('Somapala','Galle');
INSERT INTO Student VALUES(1000,'Gunapala','Matara');
INSERT INTO Student (name,address)VALUES('Sumanapala','Galle');
SQL SPECIFIC SELECTIONS
01) SELECT * FROM Student;
02) SELECT studentId FROM Student;
03) SELECT studentId,name FROM Student;
04) SELECT studentId,name AS 'StudentName' FROM Student;
05) SELECT studentId AS 'StudentID',name AS 'StudentName' FROM Student;
06) SELECT * FROM Student LIMIT 3;
07) SELECT customerId,name,address,salary,(salary*3/100) AS ETF FROM CustomerDetails;
08)
SELECT customerId,name,address,salary,(salary*3/100) AS ETF,(salary-(salary*0.03)) AS
NetSalary FROM CustomerDetails;
09)
SELECT customerId,name,address,salary,(salary*3/100) AS ETF,(salary-ETF) AS NetSalary
FROM CustomerDetails;
//Error. ETF is created on the moment temporily not in our primary table.
SQL ORDER BY CLAUSE
• The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.
EX:
SELECT * FROM CustomerDetails ORDER BY name;
SELECT * FROM CustomerDetails ORDER BY name DESC;
SELECT * FROM CustomerDetails ORDER BY name ASC;
SELECT name,address FROM CustomerDetails ORDER BY name ASC;
SQL WHERE CLAUSE
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
EX:
SELECT * FROM CustomerDetails WHERE salary>80000;
SELECT * FROM CustomerDetails WHERE salary<80000;
SELECT * FROM CustomerDetails WHERE salary<80000 AND name='Gunapala';
SELECT name,salary FROM CustomerDetails WHERE salary>80000;
SELECT name,salary FROM CustomerDetails WHERE salary>50000 ORDER BY name DESC;
SELECT name,salary,address FROM CustomerDetails WHERE salary>50000 AND
address='Panadura';
SQL JOIN CLAUSE
A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
•(INNER) JOIN: Returns records that have matching values in both tables
•LEFT (OUTER) JOIN: Returns all records from the left table, and the matched
records from the right table
•RIGHT (OUTER) JOIN: Returns all records from the right table, and the
matched records from the left table
•FULL (OUTER) JOIN: Returns all records when there is a match in either left or
right table
1) INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
2) LEFT JOIN
The LEFT JOIN keyword returns all records from the left table (table1), and the
matching records from the right table (table2). The result is 0 records from the right
side, if there is no match.
The LEFT JOIN keyword returns all records from the left table (Customers), even if
there are no matches in the right table (Orders).
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
3) RIGHT JOIN
The RIGHT JOIN keyword returns all records from the right table (table2), and the
matching records from the left table (table1). The result is 0 records from the left
side, if there is no match.
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
4) FULL JOIN
The FULL OUTER JOIN keyword returns all records when there is a match in left
(table1) or right (table2) table records.
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name
UNION
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
SQL WHERE | LIKE Statement
SELECT column1, column2,...
FROM table_name
WHERE column LIKE pattern;
EX:
01) SELECT * FROM Customer WHERE name LIKE '%pala';
02) SELECT * FROM Customer WHERE name LIKE 'S%';
03) SELECT * FROM Customer WHERE name LIKE '%na%';
04) SELECT * FROM Customer WHERE name LIKE '_i%';
SQL HAVING Clause
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
EX:
01) SELECT * FROM CustomerDetails HAVING salary>50000 ORDER BY name DESC;
02) SELECT name,salary FROM CustomerDetails HAVING salary>50000 ORDER BY name
DESC;
//SELECT name,address FROM CustomerDetails HAVING salary>50000 ORDER BY name
DESC;
(When using HAVING, the metioning of the relavant field name is mandatory.
//SELECT customerId,name,address,salary,(salary*3/100) AS ETF,(salary-(salary*3/100)) AS
NetSalary FROM CustomerDetails WHERE ETF>2000;
(Pre Checking : WHERE always check details from PRIMARY TABLE)
03) SELECT customerId,name,address,salary,(salary*3/100) AS ETF,(salary-(salary*3/100)) AS
NetSalary FROM CustomerDetails HAVING ETF>2000;
(Past Checking : HAVING always check details from new TABLE)
SQL Functions
01) SELECT VERSION();
02) SELECT CURDATE();
03) SELECT YEAR('2019-05-15');
04) SELECT MONTH('2019-05-15');
05) SELECT DATE('2019-05-15');
SQL AGGREGATE FUNCTION
EX:
01) SELECT COUNT(*) FROM Customer;
02) SELECT COUNT(customerId) FROM Customer;
03) SELECT COUNT(name) FROM Customer;
04) SELECT COUNT(DISTINCT address) FROM Customer;
05) SELECT SUM(salary) FROM Customer;
06) SELECT MIN(salary) FROM Customer;
07) SELECT MAX(salary) FROM Customer;
08) SELECT AVG(salary) FROM Customer;
09) SELECT SUM(salary),MIN(salary),MAX(salary),AVG(salary) FROM Customer;
10) SELECT Province,MAX(salary) FROM Customer GROUP BY Province;