INTRODUCTION TO SQL QUERIES REALTED BRIEF

SQL

1. SQL Introduction
WhatisSQL?
•SQLstandsforStructuredQueryLanguage
•SQLletsyouaccessandmanipulatedatabases
•SQLbecameastandardoftheAmerican
NationalStandardsInstitute(ANSI)

2. WhatCanSQLdo?
•SQLcanexecutequeriesagainstadatabase
•SQLcanretrievedatafromadatabase
•SQLcaninsertrecordsinadatabase
•SQLcanupdaterecordsinadatabase
•SQLcandeleterecordsfromadatabase
•SQLcancreatenewdatabases
•SQLcancreatenewtablesinadatabase
•SQLcancreatestoredproceduresinadatabase
•SQLcancreateviewsinadatabase
•SQLcansetpermissionsontables,procedures,andviews

3. DBMSVSRDBMS
•RDBMSstandsforRelationalDatabaseManagement
System.
•RDBMSisthebasisforSQL,andforallmoderndatabase
systemssuchasMSSQLServer,IBMDB2,Oracle,
MySQL,andMicrosoftAccess.
•ThedatainRDBMSisstoredindatabaseobjectscalled
tables.
•Atableisacollectionofrelateddataentries,anditconsistsof
columnsandrows.

4. The SQL CREATE TABLE Statement
TheCREATE TABLEstatement is used to create a new table in a database.
CREATETABLEtable_name(
column1 datatype,
column2 datatype,
column3 datatype,
....
);
Example
CREATETABLEPersons (
PersonIDint,
LastNamevarchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

5. Create Table Using Another Table
•The new table gets the same column definitions. All columns or
specific columns can be selected.
•If you create a new table using an existing table, the new table will
be filled with the existing values from the old table.
•CREATETABLEnew_table_nameAS
SELECTcolumn1, column2,...
FROMexisting_table_name
WHERE....;
•CREATETABLETestTableAS
SELECTcustomername, contactname
FROMcustomers;

6. SQL Commands
•SELECT-extracts data from a database
•UPDATE-updates data in a database
•DELETE-deletes data from a database
•INSERT INTO-inserts new data into a database
•CREATE DATABASE-creates a new database
•ALTER DATABASE-modifies a database
•CREATE TABLE-creates a new table
•ALTER TABLE-modifies a table
•DROP TABLE-deletes a table
•CREATE INDEX-creates an index (search key)
•DROP INDEX-deletes an index

7. These SQL commands are mainly categorized into
five categories:
1.DDL–Data Definition Language
2.DQL–Data Query Language
3.DML–Data Manipulation Language
4.DCL–Data Control Language
5.TCL–Transaction Control Language

9. SQL Queries
•SELECT*FROMCustomers;
•SELECTCustomerName, CityFROMCustomers;
•SELECTDISTINCTCountryFROMCustomers;
•SELECTCOUNT(DISTINCTCountry)FROMCustomers;
•SELECTCount(*)ASDistinctCountries
FROM(SELECTDISTINCTCountryFROMCustomers);

10. •SELECT*FROMCustomers WHERECountry='Mexico’;
•SELECT*FROMCustomers WHERECustomerID>80;
•SELECT*FROMProducts ORDERBYPrice;
•SELECT*FROMProducts ORDERBYPriceDESC;
•SELECT*FROMProducts ORDERBYProductName;
•SELECT*FROMProducts ORDERBYProductNameDESC;
•SELECT*FROMCustomers ORDERBYCountry, CustomerName;

11. •SELECT*FROMCustomers ORDERBYCountryASC, CustomerNameDESC;
•SELECT* FROMCustomers
WHERECountry='Spain'ANDCustomerNameLIKE'G%’;
•SELECT*FROMCustomers WHERECountry ='Germany'
ANDCity ='Berlin’ ANDPostalCode>12000;
•SELECT*FROMCustomers
WHERECountry='Spain'AND(CustomerNameLIKE'G%'ORCustomerName
LIKE'R%’);
•SELECT*FROMCustomers
WHERECountry='Spain'ANDCustomerNameLIKE'G%'ORCustomerNameL
IKE'R%';

12. •SELECT* FROMCustomers
WHERECountry='Germany'ORCountry ='Spain’;
•SELECT*FROMCustomers
WHERECity='Berlin'ORCustomerNameLIKE'G%'ORCou
ntry ='Norway’;
•SELECT*FROMCustomers WHERECountry
='Spain'AND(CustomerNameLIKE'G%'ORCustomerName
LIKE'R%’);
•SELECT*FROMCustomers WHERENOTCountry='Spain';

13. •SELECT*FROMCustomers WHERECustomerNameNOTLIKE'A%’;
•SELECT*FROMCustomers
WHERECustomerIDNOTBETWEEN10AND60;
•SELECT*FROMCustomers WHERECityNOTIN('Paris','London’);
•SELECT*FROMCustomers WHERENOTCustomerID>50;
•SELECTCustomerName, ContactName, Address FROMCustomers
WHEREAddressISNULL;
•SELECTCustomerName, ContactName, Address FROMCustomers
WHEREAddressISNOTNULL;

14. •UPDATECustomers SETContactName='Alfred Schmidt', City='Frankfurt’
WHERECustomerID=1;
•UPDATECustomers SETContactName='Juan’ WHERECountry='Mexico’;
•DELETEFROMCustomersWHERECustomerName='AlfredsFutterkiste’;
•DELETEFROMtable_name;
•DROPTABLECustomers;
•SELECTTOP3*FROMCustomers;

15. SQL Clauses
•The main clauses areSELECT, FROM, WHERE, GROUP BY,
HAVING, ORDER BY, INSERT, UPDATE, DELETE, and
JOIN.
•Each clause has a syntax and its own set of rules and options.
•They can also be used in combination to create complex queries

16. SQLOperators
1.SQLArithmeticOperators(+,-,*,/,%)
2.SQLBitwiseOperators(&,|,!,^)
3.SQLComparisonOperators(=,<,>,>=,<=,<>)
4.SQLCompoundOperators(+=,-=,*=,/=,%=,&=)
5.SQLLogicalOperators(ALL,AN,ANY,BETWEEN,EXISTS,IN
LIKE,NOT,OR,SOME)

17. SQL Aggregate Functions
Aggregate functions are often used with theGROUP BYclause
of theSELECTstatement.
TheGROUP BYclause splits the result-set into groups of values and
the aggregate function can be used to return a single value for each group.

18. The most commonly used SQL aggregate functions are
MIN()-returns the smallest value within the selected column
MAX()-returns the largest value within the selected column
COUNT()-returns the number of rows in a set
SUM()-returns the total sum of a numerical column
AVG()-returns the average value of a numerical column

19. SQLData Constraints
•SQL constraints are used to specify rules for the data in a table.
•Constraints are used to limit the type of data that can go into a table.
This ensures the accuracy and reliability of the data in the table.
•If there is any violation between the constraint and the data action, the
action is aborted.
•Constraints can be column level or table level. Column level
constraints apply to a column, and table level constraints apply to the
whole table.

20. NOT NULL-Ensures that a column cannot have a NULL value
UNIQUE -Ensures that all values in a column are different
PRIMARY KEY-A combination of a NOT NULL and UNIQUE.
Uniquely identifies each row in a table
FOREIGN KEY-Prevents actions that would destroy links between tables
CHECK-Ensures that the values in a column satisfies a specific condition
DEFAULT-Sets a default value for a column if no value is specified
CREATE INDEX-Used to create and retrieve data from the database very
quickly

21. SQLJoins
AJOINclause is used to combine rows from
two or more tables, based on a related column
between them.
SELECTOrders.OrderID, Customers.CustomerName, Orders.OrderDate
FROMOrders
INNERJOINCustomersONOrders.CustomerID=Customers.CustomerID;

22. Different Types of SQL JOINs
•(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

25. •SELECTProductID, ProductName, CategoryName
FROMProducts
INNERJOINCategoriesONProducts.CategoryID=
Categories.CategoryID;
•SELECTProducts.ProductID, Products.ProductName,
Categories.CategoryName
FROMProducts
JOINCategoriesONProducts.CategoryID=
Categories.CategoryID;

26. •SELECTOrders.OrderID, Customers.CustomerName,
Shippers.ShipperName
FROM((Orders
INNERJOINCustomersONOrders.CustomerID=
Customers.CustomerID)
INNERJOINShippersONOrders.ShipperID=
Shippers.ShipperID);
•SELECTCustomers.CustomerName, Orders.OrderID
FROMCustomers
LEFTJOINOrdersONCustomers.CustomerID=
Orders.CustomerID
ORDERBYCustomers.CustomerName;
•

27. •SELECTOrders.OrderID, Employees.LastName,
Employees.FirstName
FROMOrders
RIGHTJOINEmployeesONOrders.EmployeeID=
Employees.EmployeeID
ORDERBYOrders.OrderID;
•SELECTCustomers.CustomerName, Orders.OrderID
FROMCustomers
FULLOUTERJOINOrdersONCustomers.CustomerID=Orders.C
ustomerID
ORDERBYCustomers.CustomerName;

28. SELF JOIN
•SELECTA.CustomerNameASCustomerName1,
B.CustomerNameASCustomerName2,A.City
FROMCustomers A, Customers B
WHEREA.CustomerID<> B.CustomerID
ANDA.City= B.City
ORDERBYA.City;

29. SQL Window Function Example
One important difference betweenwindow
functionsandaggregate functionsis when you use aggregate functions
with theGROUP BY clause, you lose the individual row.
This prevents you from mixing attributes of the individual row with
results of the aggregate function.
Window functions do not have this restriction, enabling you to mix the
results with record level fields.

30. One important point to note about window
functions is the placement in the SQL query.
You can invoke a window function in
theSELECTlist statement or in theORDER
BY clauseof a query, but not in
theWHERE,GROUP BYorHAVING clauses.

32. TheRANK()function is one of the simplest
window functions.
It returns the position of any row inside the
partition.

33. To obtain the rank salary for each department:
SELECT
RANK() OVER (PARTITION BY department ORDER
BY salary DESC) AS dept_rank,
department,
employee_id,
full_name,
salary
FROM employee;

35. Another interesting example is a query to obtain a metric for
every employee about how close they are from the top salary of
their department.
Here is the formula to obtain this metric:
employee_salary / max_salary_in_dept

36. This query orders all employees by the calculated metric, sorted
by employees with the lowest salary related to the max salary of
their department.
SELECT
employee_id,
full_name,
department,
salary,
salary/MAX(salary) OVER (PARTITION BY department
ORDER BY salary DESC)
AS salary_metric FROM employee