SQL Commands
First Step:
CREATE DATABASE database_name;
CREATE DATABASE School;
Second Step:
Use database_name;
Use School;
Third Step:
CREATE TABLE table_name(c_name datatype(size),…….)
CREATE TABLE Student(R_No int(20) primary key, Name varchar(25), DOB date);
Fourth Step:
INSERT INTO table_name VALUES(1,’Maha’,’1990-08-21’),(2,’Mithu’,’2017-09-18’),(3,’Visu’,’1986-11-11’);
INSERT INTO Student VALUES(1,’Maha’,’1990-08-21’),(2,’Mithu’,’2017-09-18’),(3,’Visu’,’1986-11-11’);
Common DDL Commands – towards to the (Table Structure)
Command Meaning Syntax Description
Creates a new table CREATE TABLE table_name (column1
CREATE Defines new table structure
or database datatype(size), column2 datatype(size), ... );
ADD: Add the Extra ALTER TABLE table_name ADD column_name
Column datatype;
Drop: Delete the ALTER TABLE table_name DROP COLUMN
unwanted Column column_name;
ALTER
Modify: It change
(Modifies an Add, remove, or change
the data type, ALTER TABLE table_name MODIFY
existing table columns
constrains to any column_name datatype(size);
structure)
column of the table
ALTER TABLE table_name CHANGE
CHANGE: Rename
Old_column_name new_column_name
the existing column
datatype(size);
Deletes a table or Removes the table structure
DROP DROP TABLE table_name;
database permanently and all its data
Deletes all records
Faster than DELETE (no
TRUNCATE from a table but TRUNCATE TABLE table_name;
WHERE condition)
keeps its structure
Changes the name of
RENAME RENAME TABLE old_name TO new_name; Renames an existing table
a table
Common DML Commands: Towards to the Data in the Table,
Command Meaning Syntax Description
INSERT INTO table_name VALUES (val1, val2, ...);
Ex:
INSERT INTO Student Values(2,”Maha”,’1990-08-21’)
Adds new rows
INSERT Insert new record
into a table INSERT INTO Student Values(2,”Maha”,’1990-08-21’),
(2,’Mithu’,’2017-09-18’),(3,’Visu’,’1986-11-11’);
Modifies existing UPDATE table_name SET column1 = value1, column2 = value2 WHERE Update specific
UPDATE
rows condition; records
Removes existing Deletes records
DELETE DELETE FROM table_name WHERE condition;
rows from a table (use WHERE)
DQL: (Data Query Language)
Display , Select or View – DQL (Select Command)
Select all columns:
SELECT * FROM table_name;
Select specific columns:
SELECT name, marks FROM students;
With condition:
SELECT * FROM students WHERE marks > 80;
With sorting:
SELECT * FROM students ORDER BY marks DESC;
SELECT * FROM students ORDER BY marks ASC;
SQL Queries:
WHERE Clause
The WHERE clause is used to filter records.
It is used to extract only those records that fulfil a specified condition.
SELECT * FROM Customers WHERE Country='Mexico';
Reordering Columns:
Select Name,Rollno,DOB from Student;
DISTINCT Clause:
The SELECT DISTINCT statement is used to return only distinct (different) values Which means it will avoid duplicate
values.
SELECT DISTINCT Country FROM Customers;
AND Operator
The WHERE clause can contain one or many AND operators.
SELECT * FROM CustomersWHERE Country = 'Spain' AND CustomerName LIKE 'G%';
The AND operator is used to filter records based on more than one condition, like if you want to return all customers from
Spain that starts with the letter 'G':
OR Operator
The WHERE clause can contain one or more OR operators.
SELECT * FROM Customers WHERE Country = 'Germany' OR Country = 'Spain';
The OR operator is used to filter records based on more than one condition, like if you want to return all customers from
Germany but also those from Spain:
NOT Operator
The NOT operator is used in combination with other operators to give the opposite result, also called the negative result.
SELECT * FROM Customers WHERE NOT Country = 'Spain';
In the select statement below we want to return all customers that are NOT from Spain:
The order of the precedence for logical operators (AND, OR, NOT) is NOT(!), AND (&&), OR (||)
Aggregate Functions
An aggregate function is a function that performs a calculation on a set of values, and returns a single value.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause splits
the result-set into groups of values and the aggregate function can be used to return a single value for each group.
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
Aggregate functions ignore null values (except for COUNT(*)).
The SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
NOT IN
By using the NOT keyword in front of the IN operator, you return all records that are NOT any of the values in the list.
SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');
SQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
NOT BETWEEN
To display the products outside the range of the previous example, use NOT BETWEEN:
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;
SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
• The percent sign % represents zero, one, or multiple characters
• The underscore sign _ represents one, single character
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
Starts With
To return records that starts with a specific letter or phrase, add the % at the end of the letter or phrase.
SELECT * FROM Customers WHERE CustomerName LIKE 'La%';
Ends With
To return records that ends with a specific letter or phrase, add the % at the beginning of the letter or phrase.
SELECT * FROM Customers WHERE CustomerName LIKE '%a';
The _ Wildcard
The _ wildcard represents a single character.
It can be any character or number, but each _ represents one, and only one, character.
SELECT * FROM Customers WHERE city LIKE 'L_nd__';
The % Wildcard
The % wildcard represents any number of characters, even zero characters.
SELECT * FROM Customers WHERE city LIKE '%L%';
SQL Aliases
• SQL aliases are used to give a table, or a column in a table, a temporary name.
• Aliases are often used to make column names more readable.
• An alias only exists for the duration of that query.
• An alias is created with the AS keyword.
SELECT CustomerID AS ID FROM Customers;
AS is Optional
Actually, in most database languages, you can skip the AS keyword and get the same result:
SELECT CustomerID ID FROM Customers;
Alias for Columns
The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:
SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
Using Aliases With a Space Character
If you want your alias to contain one or more spaces, like "My Great Products", surround your alias with square brackets or
double quotes.
Using [square brackets] for aliases with space characters:
SELECT ProductName AS [My Great Products] FROM Products;
Using "double quotes" for aliases with space characters:
SELECT ProductName AS "My Great Products" FROM Products;
Aliases can be useful when:
• There are more than one table involved in a query
• Functions are used in the query
• Column names are big or not very readable
• Two or more columns are combined together
The SQL ORDER BY
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
SELECT * FROM Products ORDER BY Price;
DESC
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use
the DESC keyword.
Sort the products from highest to lowest price:
SELECT * FROM Products ORDER BY Price DESC;
ORDER BY Several Columns
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the
"CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by
CustomerName:
SELECT * FROM Customers ORDER BY Country, CustomerName;
Using Both ASC and DESC
The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and
descending by the "CustomerName" column:
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Let's look at a selection from the "Orders" table:
SELECT ProductID, ProductName, CategoryName FROM Products
INNER JOIN Categories ON [Link] = [Link];
The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country
HAVING COUNT(CustomerID) > 5;
JOIN or INNER JOIN
JOIN and INNER JOIN will return the same result.
INNER is the default join type for JOIN, so when you write JOIN the parser actually writes INNER JOIN.
SELECT [Link], [Link], [Link]
FROM Products JOIN Categories ON [Link] = [Link];
LEFT JOIN Keyword
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.
SELECT [Link], [Link] FROM Customers
LEFT JOIN Orders ON [Link] = [Link]
ORDER BY [Link];
SQL RIGHT JOIN Keyword
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 [Link], [Link], [Link] FROM Orders
RIGHT JOIN Employees ON [Link] = [Link]
ORDER BY [Link];