SQL - Clauses | Where | TOP, Limit, Fetch First or Rownum Clause | Group BY | Having | Order BY

Where Clause:

The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.

Syntax

SELECT column1, column2, ... FROM table_name
WHERE condition;

Example:
:

Select all customers from Mexico:
WHERE Country='Mexico';

SELECT * FROM Customers
WHERE CustomerID=1;

SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.

SELECT TOP 3 * FROM Customers;

SELECT TOP 50 PERCENT * FROM Customers;

SELECT * FROM Customers
LIMIT 3;

SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;

SELECT * FROM Customers
FETCH FIRST 50 PERCENT ROWS ONLY;

Limit Clause:

Sets an upper limit on number of rows(tuples) to be returned.

Syntax:

Select col1,col2 from table_name

Limit number;

Example:

Select * from student 

Limit 3;

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.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

SELECT * FROM Products
ORDER BY Price;

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;

Group by clause:

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

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

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

Having clause:

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

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

The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

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

General order of commands:

Select columns

From table_name

Where condition

Group by columns

Having condition

Order by columns ASC;

SQL - Constraints

SQL Constraints:

SQL constraints are used to specify rules for data in a table.

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

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.

The following constraints are commonly used in SQL:

• 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.

Not Null:

By default, a column can hold NULL values.

The NOT NULL constraint enforces a column to NOT accept NULL values.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

ALTER TABLE Persons
MODIFY Age int NOT NULL;

Unique:

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

ALTER TABLE Persons
ADD UNIQUE (ID);

Primary Key:

• The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values, and cannot contain NULL values.
• A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

Syntax:

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

PRIMARY KEY constraint on multiple columns:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

ALTER TABLE Persons
DROP PRIMARY KEY;

Foreign Key:

• The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
• A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
• The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

Persons Table

PersonID	LastName	FirstName	Age
1	Hansen	Ola	30
	Svendson	Tove	23
3	Pettersen	Kari	20

Orders Table

OrderID	OrderNumber	PersonID
1	77895	3
2	44678	3
3	22456	2
4	24562	1

Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table. The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Check:

• The CHECK constraint is used to limit the value range that can be placed in a column.
• If you define a CHECK constraint on a column it will allow only certain values for this column.
• If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

The  following CHECK constraint ensures that the age of a person must be 18, or older:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

CHECK on multiple columns:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

ALTER TABLE Persons
ADD CHECK (Age>=18);

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

Default:

The DEFAULT constraint is used to set a default value for a column.

The default value will be added to all new records, if no other value is specified.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

Create Index:

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

CREATE INDEX idx_lastname
ON Persons (LastName);

SQL - SELECT | Select Distinct | Insert Into | Insert Multiple rows | Update | delete

SELECT statement:

It is used to select or view data from a database table.

Syntax:

SELECT column1, column2, ...
FROM table_name;

Or to get all columns

SELECT *
FROM table_name;

Example:

SELECT CustomerName, City FROM Customers;

Select distinct statement:

It is used to return or select distinct or different values.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example:

SELECT DISTINCT Country FROM Customers;

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

Insert into statement:

It is used to insert new records to a db table.

Syntax:

Two ways we can do that

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Insert Multiple Rows

It is also possible to insert multiple rows in one statement.

To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

Update:

The UPDATE statement is used to modify the existing records in a table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Delete:

The DELETE statement is used to delete existing records in a table.

DELETE FROM table_name WHERE condition

DELETE FROM Customers WHERE CustomerName='Sanddy';

Delete all records:

DELETE FROM table_name;

Delete a table:

DROP TABLE table_name;

SQL - Create your database schema | Create a table |Add data to tables | SQL data types | Types of SQL commands

Create database schema:

CREATE DATABASE DB_NAME;

Create database if not exists db_name;

DROP DATABASE DB_NAME;

Drop database if exists db_name;

Show databases:

show tables;

USE DB_NAME;

Create a table:

USE db_name;

CREATE TABLE TABLE_NAME(

column_name1 datatype constraint,

column_name2 datatype constraint,

column_name3 datatype constraint

)

Example: 

Create table student(

id INT PRIMARY KEY,

name VARCHAR(50),

age INT NOT NULL

);

Add data to tables:

INSERT INTO <TABLE_NAME> VALUES (COLUMN1_VALUE,COLUMN2_VALUE2,COLUMN3_VALUE);

Example:

INSERT INTO STUDENT VALUES (1,'SRI','36');

How to show all data of a table:

Select * from student;

SQL data types:

They define the type of values that can be stored in a column.

Types of SQL commands:

1. DDL(data definition language): create, alter, truncate and drop
2. DQL(data query language): Select
3. DML(Data manupulation language): insert, update, delete
4. DCL(data control language): Grant, Revoke permission to users.
5. TCL(transaction control  language): start transaction, commit, rollback

SQL - What is database? | Types of Databases? | What is SQL? | RDBMS? | what is a table? |

Database:

• Database is a collection of data in a format that can be easily accessed(digital).
• A software application used to manage our db is called database management system

Types of databases:

1. Relational  - data stored in tables. Ex: MySQal, Oracle, Microsoft SQL Server, Postgre SQL
2. Non relational(noSQL) - data not stored in tables. Ex: MongoDB

What is SQL:

• SQL is standard Structured Query Langugae used for storing, manipulating and retrieving data in databases.
• It is used to perform CRUD operation , Create,  read, update and delete

RDBMS:

• RDBMS stands for Relational Database Management System.
• RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
• The data in RDBMS is stored in database objects called tables.

A Table:

• A table is a collection of related data entries and it consists of columns and rows. For example, customers table.
• Every table is broken up into smaller entities called fields. The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. 
• A field is a column in a table that is designed to maintain specific information about every record in the table.
• A record, also called a row, is each individual entry that exists in a table. A record is a horizontal entity in a table.
• A column is a vertical entity in a table that contains all information associated with a specific field in a table.

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

SQL Links

 SQL links:

1. SQL in a nut shell
   
2. SQL - What is database? | Types of Databases? | What is SQL? | RDBMS? | what is a table? |
   
3. SQL - Create your database schema | Create a table |Add data to tables | SQL data types | Types of SQL commands
   
4. SQL DDL commands
   
5. SQL Constraints
   
6. SQL - DQL and DML commands - SELECT | Select Distinct | Insert Into | Insert Multiple rows | Update | delete
   
7. SQL DCL and TCL
   
8. SQL - Clauses | Where | TOP, Limit, Fetch First or Rownum Clause | Group BY | Having | Order BY
   
9. SQL - Aggregate functions
   
10. SQL Joins | Inner join | Left Join | Right Join | Full Join | Self Join
    
11. SQL Set Operations
    
12. SQL Subquery
    
13. SQL Views
    
14. SQL Operators
    
15. SQL Case Expression
    
16. SQL Sequence
    
17. SQL Null Functions
    
18. SQL wildcard characters
    
19. SQL working with dates
    

Reference:

https://www.w3schools.com/sql/default.asp