SQL

What is SQL ?

SQL stands for Structured Query Language which lets you to access and manipulate the database.

What can SQL do ?

Execute queries against a database.
Retrieve data in a database.
Insert records to a database.
Update records in a database.
Delete records in a database.
Create databases and create tables in a database.
Create views in a database.

Advantages of SQL

Faster Query processing – [Large amount of data is retrieved efficiently. Operations like insert, update, delete can be performed easily.]
No coding skills – [Operations can be performed with a less number of codes. Most of the keywords like SELECT , UPDATE , FROM etc. are familiar to any user.]
Easy to learn and understand.

Disadvantages of SQL

Cost – [some versions are expensive and programmers cannot access]
Complex Interface – [For some users it is uncomfortable when dealing with the database]

What is RDBMS ?

RDBMS stands for Relational Database Management System.
Data of RDBMS is stored in a database object called table. Table is a collection of related data and a table consists of rows and columns.
Column in a table is also called as a field while rows are known as records.

SQL Statements

*SQL Statement*	*Explanation*
SELECT * FROM Customers;	Retrieve all the data in the table called “Customers” SQL is not case sensitive. “select” and “SELECT” are same.
SELECT CustomerName, City FROM Customers;	Retrieve the columns named as “CustomerName” and “City” in the “Customers” table.
SELECT DISTINCT Country FROM Customers;	The word “DISTINCT” will select the different values of a selected column. Avoid redundancy.
SELECT COUNT(DISTINCT Country) FROM Customer;	This will display the number of different countries.
SELECT * FROM Customers WHERE Country = ‘Mexico’;	Select all the customers from the country “Mexico” , in the “Customers” table.
SELECT * FROM Customers WHERE City IN (‘Paris’ , ‘London’);	Select all the customers who’s countries “Paris” or “London”
SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;	Select all the products with the price between 50 and 60
SELECT * FROM Customers WHERE City LIKE ‘s%’	Select all the customers who’s countries start with letter ‘s’

Statements with AND , OR and NOT

SELECT * FROM Customers

WHERE Country = ‘Germany’ AND City = ‘Berlin’ ;

SELECT * FROM Customers

WHERE Country = ‘Germany’ OR City = ‘Berlin’ ;

SELECT * FROM Customers

WHERE NOT Country = ‘Germany’ ;

SELECT * FROM Customers

WHERE Country = ‘Germany’ AND ( City = ‘Berlin’ OR City = ‘München’);

SELECT * FROM Customers

WHERE NOT Country = ‘Germany’ AND NOT Country = ‘USA’;

ORDER BY keyword

Sort the result set in ascending or descending order.
SELECT * FROM Customers ORDER BY Country;	Retrieve all the customers sorted according to the Country. (it is ascending by default)
SELECT * FROM Customers ORDER BY Country DESC;	Retrieve all the customers sorted in descending order according to the Country.
SELECT * FROM Customers ORDER BY Country , customerName;	Retrieve all the customers sorted in according to the Country but if there are duplicate country names, sorts according to the customerName.
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;	Retrieve all the customers and sorts ascending according to the country and descending according to the customer name.

INSERT INTO keyword

If you have a table named “Customer” with 4 columns called “NIC” , “Name” , “Country” , “Age”;

Below query will allow you to insert data to the named columns without any error.

INSERT INTO Customer (Name, Country, Age) VALUES (‘Edward’ , ‘America’ , 34);

But if you use the below query, you must insert data to all the columns. Otherwise it will course an error

INSERT INTO Customer VALUES (2143875498 , ‘Edward’ , ‘America’ , 34);

NULL keyword

SELECT CustomerName FROM Customers

WHERE Address IS NULL;

Retrieve the names of the customers whose address is null

SELECT CustomerName FROM Customers

WHERE Address IS NOT NULL;

Retrieve the names of the customers whose address is not null

UPDATE keyword

Below query updates the customer’s name whose id is “1”

UPDATE Customer
SET name = ‘Edward Patric’, city = ‘Sydney’
WHERE id = 1;

DELETE keyword

This keyword is used to delete and existing record of a table.

DELETE FROM Customer WHERE CustomerName = ‘Edward’;

The below code will delete all the rows of the “Customer” table

DELETE FROM Customer

SELECT TOP , LIMIT keywords

When you have 100 of records in a table, the below queries will select the 1^st three records.

1^st method ->

SELECT TOP 3 * FROM Customer ;

2^nd method ->

SELECT * FROM Customer
LIMIT 3 ;

Retrieve the 1^st 50% of the records from the whole table.

SELECT TOP 50 PERCENT * FROM Customer ;

The below queries will retrieve the 1^st three records which satisfy the given condition.

1^st method ->

SELECT TOP 3 * FROM Customer
WHERE Country = ‘Germany’;

2^nd method ->

SELECT * FROM Customer
WHERE Country = ‘Germany’
LIMIT 3 ;

SQL MIN() and MAX() functions

MIN() returns the smallest value from the selected column. Smallest price will be printed from the column “Price” in the “Products” table. It will be printed under a column named “SmallestPrice”.

SELECT MIN(Price) AS SmallestPrice
FROM Products ;

MAX() returns the largest value from the selected column. Largest price will be printed from the column “Price” in the “Products” table. It will be printed under a column named “LargestPrice”.

SELECT MAX(Price) AS LargestPrice
FROM Products ;

SQL COUNT(), AVG() and SUM()

Find the number of products. This will not count the null values.

SELECT COUNT(ProductID)
FROM Products;

Find the average price of all the products. Null values are ignored.

SELECT AVG(Price)
FROM Products;

Find the sum or the total of the quantity. Null values are ignored.

SELECT SUM(Quantity)
FROM Orders;

SQL LIKE Operator

SELECT * FROM Customers WHERE name LIKE ‘a%’ ;	Selects all the customers whose names starting with ‘a’
SELECT * FROM Customers WHERE name LIKE ‘%a’ ;	Selects all the customers whose names ending with ‘a’
SELECT * FROM Customers WHERE name LIKE ‘%or%’ ;	Selects all the customers whose names has ‘or’ in anywhere of the name.
SELECT * FROM Customers WHERE name LIKE ‘_r%’ ;	Selects all the customers whose names has ‘r’ as the 2^nd letter of the name.
SELECT * FROM Customers WHERE name LIKE ‘a__’ ;	Selects the customers whose names start with ‘a’ and has 3 letters for the name. (two underscores are typed)
SELECT * FROM Customers WHERE name LIKE ‘a%o’ ;	Selects the customers whose names start with ‘a’ and ends with ‘o’
SELECT * FROM Customers WHERE name NOT LIKE ‘a%’ ;	Selects the customers whose names does not start with ‘a’

The Wildcard characters

Wildcard characters are used to substitute one or more characters in a string. Examples for wildcards are % , _ , [charlist] , [!charlist]

SELECT * FROM Customers WHERE name LIKE ‘s%’ ;	Select the customers whose names starting with ‘s’
SELECT * FROM Customers WHERE city LIKE ‘_o_don’ ;	Selects the customers whose name of the city starts with any character, followed by ‘o’, followed by any character, followed by ‘don’
SELECT * FROM Customers WHERE name LIKE ‘[abc]%’ ;	Selects the customers whose name of the city starts with ‘a’ or ‘b’ or ‘c’
SELECT * FROM Customers WHERE name LIKE ‘[a-f]%’ ;	Selects the customers whose name of the city starts with letters ‘a’ to ‘f’. It means ‘a’ or ‘b’ or ‘c’ or ‘d’ or ‘e’ or ‘f’.
SELECT * FROM Customers WHERE name LIKE ‘[!abc]%’ ;	Selects the customers whose name of the city does not start with ‘a’ or ‘b’ or ‘c’

SQL IN operator

IN operator allows you to include multiple values in the WHERE clause. Shorthand for OR operator.

Select the customers who live in ‘German’ or ‘France’ or ‘Australia’

SELECT * FROM Customers
WHERE country IN( ‘German’ , ‘France’ , ‘Australia’ ) ;

Select the customers who do not live in ‘German’ or ‘France’ or ‘Australia’

SELECT * FROM Customers
WHERE country NOT IN( ‘German’ , ‘France’ , ‘Australia’ ) ;

Select the customers whose country is same as supplier

SELECT * FROM Customers
WHERE Country IN(SELECT country FROM Supplier );

BETWEEN keyword in depth

SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;	Select the products where the price is not in the range of 10 and 20.
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID NOT IN(1 , 2 , 3);	Select the products where the price is between 10 and 20 but do not display the products whose category id is not 1 or 2 or 3
SELECT * FROM Customer WHERE country BETWEEN ‘America’ AND ‘London’	Selects the customers whose country name lies between ‘America’ and ‘London’ words according to the alphabetical order.
SELECT * FROM Orders WHERE orderDate BETWEEN ‘2020-07-01’ AND ‘2020-07-31’	selects all orders with an OrderDate between '01-July-2020 and '31-July-2020

SQL Aliases

Used to give temporary names for columns of tables.

Select the id , phone and the name of the customer from the ‘Customers’ table and when displaying them rename the id column as ‘ID’ , phone number as ‘Contact Number’ and the name column as ‘Customer’

SELECT customerID AS ID , customerName AS Customer , phone AS [Contact Number]
FROM Customers;

Below query will show how to concatenate few columns and display as a single column

SELECT CustomerName , Address + ‘ , ’ + City + ‘ , ’ + Country AS Address
FROM Customers;

SQL Joins

INNER JOIN

Returns the records that have matching values in both tables.

If there is a match between the columns of customer and order tables, only those records will be displayed

SELECT o.orderID , c.customerName
FROM Orders o
INNER JOIN Customers c
ON o.customerID = c.CustomerID ;

You can join 3 tables to as below:
SELECT o.orderID , c.customerName
FROM Orders o
INNER JOIN Customers c
ON o.customerID = c.CustomerID
INNER JOIN Shipper s
ON o.shipperID = s. shipperID;

LEFT JOIN

Returns all the records from the left table (Table 1), and matched records from the right table(Table 2).

Returns all the records in the Orders table and only the matching records will be returned from the Customers table. If there is no matching customer for a particular order, customer columns of that order will remain as null

SELECT o.orderID , c.customerName
FROM Orders o
LEFT JOIN Customers c
ON o.customerID = c.CustomerID ;

RIGHT JOIN

Returns all the records from the right table (Table 2), and matched records from the left table.

Returns all the records in the Customers table and only the matching records will be returned from the Orders table. If there is no matching order for a particular customer, order for that customer will remain as null

SELECT o.orderID , c.customerName
FROM Orders o
RIGHT JOIN Customers c
ON o.customerID = c.CustomerID ;

FULL OUTER JOIN

Returns all the records when there is match in either left table (Table 1) or right table (Table 2) .

SQL UNION statement

Used to combine 2 or more SELECT statements.

SELECT City FROM Customers UNION SELECT City FROM Suppliers	Displays the cities from both Customers and Suppliers table. (Ignores the duplicate names)
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers	Displays the cities from both Customers and Suppliers table. (Will not ignore the duplicate names)

SQL GROUP BY statement

Groups the rows which has the same value.

List the number of customers in each country.(Imagine you have a table called ‘Customers’)

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

SQL HAVING Clause

The reason of introducing ‘HAVING’ clause to SQL is, the ‘WHERE’ clause does not allow to use aggregate functions.

List the number of customers in each country

SELECT COUNT(customerID) , country
FROM Customers
GROUP BY country;

Then reduce the above selected list only to include countries with more than 5 customers.

SELECT COUNT(customerID) , country
FROM Customers
GROUP BY country
HAVING COUNT(customerID) > 5;

Then sort the above list in descending order of the number of customer in a country.

SELECT COUNT(customerID) , country
FROM Customers
GROUP BY country
HAVING COUNT(customerID) > 5
ORDER BY COUNT(customerID) DESC;

The correct order of using different clauses we learnt is follows:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

SQL EXISTS operator

Checks the existence of a record in a sub query. Returns TRUE if the subquery returns one or more records.

The below query will return TRUE and lists the suppliers with a product price less than 20:

SELECT suplierName
FROM Suppliers
WHERE EXISTS (
  SELECT productName
  FROM Products
  WHERE Products.supplierID = Supplier. supplierID AND Price < 20
)

SQL ANY operator

Returns TRUE if any of the subqueries meet the condition.

Select the product names if it(sub query) finds any record in the OrderDetails table which has quantity equal to 10

SELECT productName
FROM Products
WHERE productID = ANY (
  SELECT productID
  FROM OrderDetails
  WHERE quantity = 10
);

SQL ALL operator

Returns TRUE only if all the subquery meets the condition. If at-least one record from the subquery false the condition, ‘ALL’ operator will return FALSE.

Select the product names if all the records from the subquery in the OrderDetails table has the quantity equal to 10.

SELECT productName
FROM Products
WHERE productID = ANY (
  SELECT productID
  FROM OrderDetails
  WHERE quantity = 10

);

SQL SELECT INTO statement

Copies data from one table to a new table.

Copy the data of the customers table to a new table called ‘CustomerBackup’

SELECT * INTO CustomerBackup
FROM Customers;

Copy the customer name and the id from the customers table to a new table called CustomerBackup which is in another database called Backup.mdb

SELECT customerName , customerID INTO CustomerBackup IN ‘Backup.mdb’
FROM Customers;

SQL INSERT INTO SELECT statement

Copy data from one table and inserts into another table. Data types of the columns in both tables should match.

Copy some data from Suppliers table into customers table. Columns that might not fill will remain as NULL.

Copy the supplier name , city and the country, then insert into the customer table.

INSERT INTO Customers(customerName , city , country)
SELECT supplierName , city , country FROM Suppliers ;

Do the same thing as above but in addition, add a condition to insert only the suppliers from Germany

INSERT INTO Customers(customerName , city , country)
SELECT supplierName , city , country FROM Suppliers
WHERE country = ‘Germany’;

SQL CASE statement

It is like if-else statement. Goes through the conditions and returns the result if it meet any condition. Once it met any condition, it will stop doing through the other conditions and returns the result. If does not meet any condition, then will return the ELSE part. If there is no ELSE part, then will return NULL.

The below query will go through the conditions and return a value when the 1^st condition is met.

SELECT OrderID , Quantity,
CASE
  WHEN Quantity > 30 THEN ‘The quantity is greater than 30’
  WHEN Quantity = 30 THEN ‘The quantity is 30’
  ELSE ‘The quantity is under 30’
END AS QuantityText
FROM OrderDetails;

[the below screen shot is taken from w3schools - https://www.w3schools.com/sql/trymysql.asp?filename=trysql_case ]

SQL Stored PROCEDURE

If there is a SQL code that we write again and again, we can use stored procedure to save it and can reuse it when we need. Also we can pass parameters to the procedures. So that the stored procedure can act according to the parameter that is passed.

Create a stored procedure named ‘SelectAllCustomers’ that selects all the customers from the ‘Customers’ table.

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Execute the above stored procedure

EXEC SelectAllCustomers;

Create a stored procedure called ‘SomeCustomers’ that selects customers from a particular city with a particular postal code from the ‘Customer’ table.

CREATE PROCEDURE SomeCustomers @City nvarchar(30) , @Code nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND Code = @Code
GO;

Execute the above procedure

EXEC SomeCustomers @City = ‘London’ , @Code = ‘WA1 1DP’;

SQL comments

Comments are used to explain the code. Comments do not execute.

--select all
SELECT * FROM Customers
/*
This is a
Multi line comment
*/

SQL CREATE TABLE statement

Used to create new tables in the database.

CREATE TABLE Persons(
personID INT,
lastName VARCHAR(255),
firstName VARCHAR (255),
address VARCHAR (255),
) ;

SQL DROP TABLE statement

Deletes a table.

DROP TABLE Persons;

SQL ALTER TABLE statement

Used to add, delete and modify columns & to add and drop constraints in an existing table.

Add a columns named ‘Email’ to the ‘Customer’ table.

ALTER TABLE Customer
ADD Email VARCHAR(255);

Delete the column named ‘Email’ from the ‘Customer’ table

ALTER TABLE Customer
DROP COLUMN Email;

Change the data type of the column named ‘DateOfBirth’ in the ‘Persons’ table

ALTER TABLE Persons
ALTER COLUMN DateOfBirth YEAR;

SQL Constraints

Used to specify rules for the data in the table.

There are few constraints that are commonly used in SQL. They are : NOT NULL , UNIQUE , PRIMARY KEY , FOREIGN KEY , CHECK , DEFAULT .

NOT NULL – (By default a column can hold null values. But adding a constraint in NOT NULL will enforce a column to not to hold null values)

CREATE TABLE Persons(

personID INT NOT NULL,

lastName VARCHAR(255) NOT NULL,

firstName VARCHAR (255) NOT NULL,

age INT

) ;

--NOT NULL in ALTER TABLE

ALTER TABLE Persons

MODIFY age INT NOT NULL;

UNIQUE – (Ensures that all the values in a column is unique)

CREATE TABLE Persons(

personID INT NOT NULL UNIQUE,

lastName VARCHAR(255),

indexNo INT

) ;

--UNIQUE in ALTER TABLE

ALTER TABLE Persons
ADD UNIQUE(indexNo);

--another method on adding a unique constraint

/*CREATE TABLE Persons(

personID INT NOT NULL,

lastName VARCHAR(255),

indexNo INT

) ;

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE(personID , indexNo);

--deleting the unique constraint

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

PRIMARY KEY – (Uniquely identifies a record in a table. This should be a unique value and cannot contain null values.)

CREATE TABLE Persons(

personID INT NOT NULL PRIMARY KEY,

lastName VARCHAR(255),

indexNo INT

) ;

/*PRIMARY KEY in ALTER TABLE(This is the other method of assigning primary key)*/

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY(personID);

--deleting the primary key constraint

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

FOREIGN KEY – (Used to prevent from getting destroyed the links between tables.)

CREATE TABLE Orders(

orderID INT NOT NULL PRIMARY KEY,

orderNumber INT NOT NULL,

personID INT FOREIGN KEY REFERENCES Persons(personID)

) ;

/*FOREIGN KEY in ALTER TABLE(This is the other method of assigning foreign key)*/

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY(personID) REFERENCES Persons(personID);

--deleting the foreign key constraint

ALTER TABLE Persons
DROP CONSTRAINT FK_PersonOrder;

CHECK – (Limits the range of values that can be stored in a column)

CREATE TABLE Persons(

personID INT NOT NULL,

lastName VARCHAR(255) NOT NULL,

firstName VARCHAR (255) NOT NULL,

age INT,

CONSTRAINT CHK_Persons CHECK(Age >= 18)

) ;

/*CHECK in ALTER TABLE(This is the other method of adding limitations)*/

ALTER TABLE Orders
ADD CONSTRAINT CHK_PersonsAge
CHECK(Age >= 18);

--deleting the check constraint

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonsAge;

DEFAULT – (Used to set default values for a column)

CREATE TABLE Persons(

personID INT NOT NULL,

lastName VARCHAR(255) NOT NULL,

firstName VARCHAR (255) NOT NULL,

age INT,

city VARCHAR(255) DEFAULT ‘Sandnes’

) ;

/*DEFAULT in ALTER TABLE(This is the other method of adding default value)*/

ALTER TABLE Persons
ADD CONSTRAINT df_city
DEFAULT ‘Sandnes’ FOR city;

--deleting the default constraint

ALTER TABLE Persons

ALTER COLUMN city DROP DEFAULT;

SQL AUTO INCREMENT field

Allows a unique number to be generated when a new record is inserted into the tables. Mostly this would be the primary key.

CREATE TABLE Persons(

personID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

lastName VARCHAR(255),

indexNo INT

) ;

Technologies that a Software Engineer must know

Search This Blog

SQL

Comments

Post a Comment