SQL

A Database is a collection of data which is organized for easy access and efficient management and updating.

It is made of tables that store the relevant information.

Database Tables

A table stores data in an organized way consisting of rows and columns similar to the one which we see in Microsoft Excel.

Database often contains multiple tables each created and designed for a specific purpose. For example we create a table of Patient Names and their contact details in a particular hospital. 

Each table has own set of fields based on the data it can store. 

A Table can have specified number of columns but can have any number of rows.

Primary Key

A Primary key is a field in the table that uniquely defines the table records. It must contain a unique value for each row and must not contain NULL values. 

For Example , The column which contains the value ID would be a good choice for a primary key in the table. 

Tables are limited to ONE primary key each and its value must be different for each row. 

What is SQL?

SQL stands for Structured Query Language which is used to access and modify the database

SQL can :

• insert, delete or update records in a database
• create a new database, tables
• retrieve data from a database

Basic SQL Commands:

-->The SQL SHOW statement is used to display the information present in the database. 

Example: SHOW DATABASES-> This command is used to list the databases managed by the server. 

                SHOW TABLES-> This command is used to all of the tables in the currently selected MySQL Database

                SHOW COLUMNS displays the information about the columns in a given table. 

                Example: SHOW COLUMNS FROM customers

• Field : Column Name
• Type: Column data type
• Key: indicates about whether the column is indexed
• Default: default value assigned to the column
• Extra: May contain additional information that is available about a given column

-->The SQL SELECT Statement is used to select data from Database. The Result is stored in a result table which is called result-set.

A query may retrieve information from selected columns or from all columns in the table. 

Syntax: SELECT column_list

               FROM table_name

Example: SELECT first_name FROM customers

CASE INSENSITIVITY:

SQL is case insensitive

select City from customers;
SELECT City FROM customers;
sElEct City From customers;

The above commands are equivalent and produce the same result

Syntax Rules:

A single SQL statement can be placed on one or more text lines. In addition, multiple SQL statements can be combined on a single text line

White spaces and multiple lines are ignored in SQL.
For example, the following query is absolutely correct.

SELECT City


FROM customers;

Selecting Multiple columns:

 You can select multiple columns from the table at once. Just list the column names, separated by commas.

Example: SELECT FirstName, LastName, City

                FROM Customers;

Selecting All columns:

To retrieve all information contained in your table, place an asterisk(*) sign after the SELECT command.

Example: SELECT * FROM Customers

The DISTINCT Keyword:

In situations, when we have multiple duplicate records in table, it is sensible to display only unique elements, instead of the duplicates. 

So the DISTINCT keyword is used along with SELECT to remove duplicate records.

Syntax: SELECT DISTINCT column_name1, column_name2

                FROM table_name;

The LIMIT Keyword:

If we need to retrieve only subset of the records, we use the LIMIT Keyword.

Syntax: SELECT column_list

               FROM table_name

                LIMIT[number_of_records]

You can also pick up a set of records from a particular offset.
In the following example, we pick up four records, starting from the third position:

SELECT ID, FirstName, LastName, City

FROM customers OFFSET 3 LIMIT 4;

Fully Qualified Names:

In SQL, we can provide table name prior to the column name, by separating with a dot.

The following statements are equivalent:

SELECT City FROM customers;

SELECT customers.City FROM customers;

ORDER BY:

It is used with SELECT to sort the returned data.

Example: 

SELECT * FROM customers

ORDER BY FirstName;

the rows are ordered alphabetically by the FirstName column.

Sorting MULIPLE Columns:

ORDER BY can sort retrieved data by multiple columns. When using ORDER BY with more than one column, separate the list of columns to follow ORDER BY with commas.

The WHERE Statement:

The WHERE Clause is used to extract only those records that fulfill a specified criterion.

Syntax:

SELECT column_list

FROM table_name

WHERE condition;  

SQL Operators:

Comparison and Logical operators are used in WHERE to filter the data to be selected

Example:

SELECT * FROM customers

WHERE ID!=7;

The BETWEEN Operator:

The BETWEEN Operator selects values within a range, lower and upper bound

Example: 

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Text Values:

When working with text columns, surround any text that appears in the statement with single quotation marks (').

The following SQL statement selects all records in which the City is equal to 'New York'.

SELECT ID, FirstName, LastName, City

FROM Customers

WHERE City = 'NEW YORK';

Logical Operators:

Logical Operators can be used to combine two boolean values and return True, False and Null.

When retrieving data using SELECT Statement, use logical operators in WHERE clause to combine multiple conditions.

 The SQL AND, OR can be combined to test multiple conditions in a query

Example:

SELECT * FROM customers

WHERE city = "NEW YORK"

AND (Age=30 OR Age=40);

The IN Operator:

The IN operator is used when you want to compare a column with more than one value.

Example:

SELECT * FROM Customers

WHERE city IN('NEW YORK', 'LOS ANGELES', 'CHICAGO')

The NOT IN Operator:

The NOT IN operator is used to exclude a list of specific values from the result set.

Example:

SELECT * FROM customers
WHERE City NOT IN ('New York', 'Los Angeles', 'Chicago');

The CONCAT Function:

It is used to concatenate two or more values and returns the concatenating string.

Example: SELECT CONCAT(FirstName, ',' ,City) FROM Customers;

The AS Keyword:

A concatenation results in a new column. You can assign a custom name to this column using the AS Keyword.

Example: SELECT CONCAT(FirstName, ',' ,City) AS new_column

                 FROM Customers;

 

Arithmetic Operators:

These operators are used to perform arithmetic operations on the column such Addition, Subtraction, Multiplication, Division.

Example: SELECT ID, FIRSTNAME, LASTNAME, SALARY+500 as SALARY

                FROM EMPLOYEES;

The UPPER Function:

The UPPER Function converts all the characters into uppercase and similarly, the LOWER Case Function converts the strings to LOWER Case.

Example: SELECT Firstname, UPPER(Lastname) AS Lastname

                FROM Employees;

SQRT and AVG:

The SQRT Function returns the square root of the given value in the argument.

Example: SELECT Salary, SQRT(Salary)

                FROM Employees

The AVG Function returns the average of the numeric column:

Example: SELECT AVG(Salary) FROM Employees;

The SUM Function:

The SUM Function is used to get the total value of a specific column.

Example: SELECT SUM(Salary) FROM EMPLOYEES

Subqueries:

A Subquery is a query within a query. 

Example:

SELECT AVG(Salary) FROM Employees;

SELECT Firstname, Salary FROM Employees

WHERE Salary>3100

ORDER BY Salary DESC;

The DESC Keyword sorts the results in descending order and ASC sorts the results in ascending order.

The above query can also be re-written as :

SELECT Firstname, Salary FROM Employees

WHERE Salary>(SELECT AVG(Salary) FROM Employees;)

ORDER BY Salary DESC;

Enclose the subquery in parentheses and there is no semicolon at the end of a subquery.

The LIKE Operator:

The LIKE Keyword is useful when specifying the search condition within your WHERE clause.

SYNTAX:

SELECT column_name

FROM table_name

WHERE column_name LIKE pattern;

SQL pattern matching enables you to use "_" to match any single character and "%" to match any arbitrary number of  characters(including zero).

For Example, if we want to select Employees whose Fistname starts with A, then:

SELECT * FROM Employees

WHERE Firstname LIKE 'A%";

As another example, the following SQL query selects all employees with a LastName ending with the letter "s":

SELECT * FROM Employees

WHERE Firstname LIKE '%s';

The MIN Function:

The MIN Function is used to return the minimum value of an expression in the SELECT Statement

Example: To know the minimum value of salary in the customers

SELECT MIN(Salary) AS Salary FROM Employees;

Joining Tables:

It is one of the most beneficial feature of SQL to combine data from one or more tables. A Table join creates a temporary table showing the data from the joined tables.

To join two tables, specify them as comma-separated lists in the FROM clause.

Example:

SELECT customers.id, customers.name, orders.name, orders.amount

FROM customers, orders

WHERE customers.id=orders.customer_id

ORDER BY customers.id;

Custom Names:

Custom names can be used for tables as well.

Example:

SELECT ct.id, ct.name, ord.name, ord.amount

FROM customers AS ct, orders AS ord

WHERE ct.id=ord.cutomer_id

ORDER BY ct.id;

Types of Join:

• INNER Join
• LEFT Join
• RIGHT Join

INNER Join is equal to JOIN, where it returns rows when there is match between the tables.

Example:

    SELECT column_name(s)
    FROM table1 INNER JOIN table2
    ON table1.column_name=table2.column_name;

LEFT Join returns all the rows form the left table even if there are not matches in the right table.

Example:

    SELECT table1.column1, table2.column2...

    FROM table1 LEFT JOIN table2
    ON table1.column_name = table2.column_name;

If no match is found for a articular row, NULL is returned.

RIGHT Join returns all the rows from the right table even if there is no matches in the left table.

Example:

    SELECT table1.column1, table2.column2...

    FROM table1 RIGHT JOIN table2
    ON table1.column_name = table2.column_name;

SET Operation:

To combine data from multiple tabled into one dataset, we use UNION and UNION ALL operators.

UNION : combines multiple datasets into a single dataset and removes any existing duplicates.

UNION ALL : combines multiple datasets into one datasets, but does not remove duplicate rows.

UNION:

This is used to combine the result-sets of two or more SELECT statements.

All SELECT statements with UNION operator must have the same number of columns.

SYNTAX:

SELECT column_names FROM table1    

UNION

SELECT column_names FROM table2

If your columns don't match exactly across all queries, you can use a NULL (or any other) value such as:

SELECT FirstName, LastName, Company FROM businessContacts
UNION
SELECT FirstName, LastName, NULL FROM otherContacts;

UNION ALL:

It selects all rows from each table and combine them into single table.

SYNTAX:

SELECT column_names FROM table1    

UNION

SELECT column_names FROM table2

Inserting Data:

The INSERT INTO statement is used to insert rows of data to a table into the database.

SYNTAX:

INSERT INTO table_name

VALUES (value1, value2, value3....)

NOTE : Make sure the order of the values is in the same order as the columns in the table.

Example:

INSERT INTO employees

VALUES (8, 'RAM', 'Young', 29);

Alternatively, we can also enter the column_names in the INSERT INTO statements.

Syntax:

INSERT INTO table_name (column_name1. column_name2, column_name3.....)

VALUES (value1, value2, value3....)

Example:

INSERT INTO table_name (ID. FirstName, LastName, Age)

VALUES (8, 'RAM', 'Young', 29);

NOTE: You can specify your own column order, as long as the values are specified in the same order as the columns.

It is also possible to insert data into specific columns only:

Example:

INSERT INTO table_name (ID. FirstName,Age)

VALUES (8, 'RAM', 29);

Updating Tables:

The UPDATE statement allows us to alter the data in the table.

SYNTAX:

UPDATE table_name

SET column1=value1, column2=value2, ...

WHERE condition;

NOTE: If you omit WHERE clauses, all the records in the table will be updated.

Updating Data:

Example:

UPDATE Employees

SET salary=5000

WHERE ID=1;

Updating Multiple columns:

Example:

UPDATE Employees

SET salary=5000, FirstName='RAM'

WHERE ID=1;

Deleting Data:

DELETE FROM table_name

WHERE condition;

NOTE : 

If you omit the WHERE clause, all records in the table will be deleted!

The DELETE statement removes the data from the table permanently.

CREATE statement:

THE CREATE TABLE statement is used to create a new table.

Creating a basic table involves naming the table and defining its columns and each column's data type.

SYNTAX:

CREATE TABLE table_name

(

column_name1 data_type(size),

column_name2 data_type(size),

column_name3 data_type(size),

.

.

.

.

.

.

);

Example:

CREATE TABLE Users

(

UserID int,

FirstName varchar(100),

LastName varchar(100),

City varchar(100),

PRIMARY KEY(UserID)

);

SQL Constraints:

SQL Constraints are used to specify rules for the table data.

• NOT NULL- Indicates that the column cannot contain any NULL value.
• UNIQUE- Does not allow to insert a duplicate value in a column.
• PRIMARY KEY- Enforces the table to accept unique data to a column and also creates a unique index for accessing faster
• CHECK- Determine whether the value is valid or not form a logical expression.
• DEFAULT- While inserting data, when no value is supplied to the column, then the column gets the value set to DEFAULT.

Example:     name varchar(100) NOT NULL;

AUTO INCREMENT:

Auto-Increment allows a unique number to be generated when a new record is inserted into the table.

Example:

UserID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (UserID);

By default, the starting value of AUTO INCREMENT is 1.

Using Constraints:

Example:

CREATE TABLE Users 

(

id int NOT NULL AUTO_INCREMENT,

username varchar(100) NOT NULL,

password varchar(10) NOT NULL,

PRIMARY KEY(id)

);

Alter Table:

The Alter command is used to add, delete, or modify columns in an existing table.

Example: ALTER TABLE People ADD DateOfBirth date;

All rows will have default value in the newly added column, which in this case is NULL.

Example: ALTER TABLE People DROP COLUMN DateOfBirth;

The column, along with the data will be completely removed from the table.

To delete the entire table:

DROP TABLE People;

Example: ALTER TABLE People RENAME FirstName TO name;

                  RENAME TABLE People To Users;

VIEWS:

In SQL, a VIEW is a virtual table that is based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Views allow us to:
- Structure data in a way that users or classes of users find natural or intuitive.
- Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
- Summarize data from various tables and use it to generate reports.

To create a view:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Updating a VIEW:

You can update a view by using the following syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;