SQL Basics To Intermediate Topics

This post talks about SQL:

SQL stands for Structured Query Language, the name itself states that this language is used to query the existing database with certain operations and with SQL we can create tables so on. SQL commands are categorized into 4 categories. They are

1) DDL (Data Definition Language): It is used to define the database structure and schema. Commands used in the DDL are

- CREATE (this command is used to create new table, table from existing table, index, function, views, store procedure and triggers)

Syntax:

- ALTER (this command is used to change the structure of existing database)

- DROP (this command is used to delete an object from database)

- TRUNCATE (this command is used to remove all records from a table, including all spaces allocated for the records are removed)

- COMMENT (this command is used to add comments to the data dictionary)

- RENAME (this command is used to rename an object existing in the database)

2) DML (Data Manipulation Language): These are used to manipulate data within database. Commands used in the DML are

- SELECT (this command is used to retrieve the data from database/table)

- INSERT (this command is used to insert a data into a table)

- UPDATE (this command is used to update existing data in a table)

- DELETE (this command is used to delete a specific record in a table or delete all records in the table)

3) DCL (Data Control Language): These are used to control access on database. Commands used in the DCL are

- GRANT (this command is used to grant the user’s access privilege to database)

- REVOKE (this command is used to withdraw user’s access privileges given by using the GRANT command)

4) TCL (Transaction Control Language): These are used to deal with a transaction within a database. Commands used in TCL are

- COMMIT (this command is used to commit the changes that we made to the table. If we don’t commit these changes then the changes will not visible)

- ROLLBACK (this command is used to manually rollback transactions which was committed before. In the case where one of the queries in a group of queries executed by a transaction fails, all the previously executed queries are rollbacked. Transactions in the SQL server are rollbacked automatically. However, with the rollback SQL statement, you can manually rollback a transaction based on certain conditions.)

- SAVEPOINT (this command is used to identify a point in a transaction to which you can later rollback it. Specify the name of the savepoint to be created. Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, then the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.)

- SET TRANSACTION (to establish the current transaction as read-only or read/write, establish its isolation level, or assign it to a specified rollback segment. The operations performed by a SET TRANSACTION statement affect only your current transaction, not other users or other transactions. Your transaction ends whenever you issue a COMMIT or ROLLBACK statement)

NOTE: We also have different databases like OLTP (Online Transactional Processing – which is used as relational DB or used for capturing day to day transactions) and OLAP (Online Analytical Processing - Which is known as DWH db!). These two DB’s are used in ETL side.

SQL
Commands with examples:
 Ø Joins
1)      Inner Join
Join: It retrieves only matching rows between both the tables.

Syntax:
        SELECT [COLUMN_LIST]
        FROM TABLE1
        INNER JOIN TABLE2
        ON
        TABLE1.COLUMNNAME = TABLE2.COLUMNNAME;
 
2)      Left Join:
Join: The LEFT JOIN keyword returns all records from the left table
(table1), and the matched records from the right table (table2). The result is
NULL from the right side, if there is no match.
 
 
             

Syntax:
SELECT [COLUMN_LIST]
FROM TABLE1
LEFT JOIN TABLE2
ON
TABLE1.COLUMNNAME =
TABLE2.COLUMNNAME;
 
Example:
SELECT CUSTOMERS.CUSTOMERNAME,
ORDERS.ORDERID
FROM CUSTOMERS
LEFT JOIN ORDERS
ON
CUSTOMERS.CUSTMERID =
ORDERS.CUSTOMERID;
 
3)      Right Join:
Join: The RIGHT JOIN keyword returns all records from the right table
(table2), and the matched records from the left table (table1). The result is
NULL from the left side when there is no match.
                       

Syntax:
SELECT [COLUMN_LIST]
FROM TABLE1
RIGHT JOIN TABLE2
ON
TABLE1.COLUMNNAME =
TABLE2.COLUMNNAME;
Example:
SELECT CUSTOMERS.CUSTOMERNAME,
ORDERS.ORDERID
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON
CUSTOMERS.CUSTMERID =
ORDERS.CUSTOMERID;
 
4)      Full Join:
Join: The FULL OUTER JOIN keyword returns all records when there is a match
in left (table1) or right (table2) table records.
Note: FULL OUTER JOIN
can potentially return very large result-sets!
Tip: FULL OUTER JOIN and
FULL JOIN are the same.
                 

  Syntax:
SELECT [COLUMN_LIST]
FROM TABLE1
FULL OUTER JOIN TABLE2
ON
TABLE1.COLUMNNAME =
TABLE2.COLUMNNAME
<<ANY CONDITION>>;
Example:
SELECT CUSTOMERS.CUSTOMERNAME,
ORDERS.ORDERID
FROM CUSTOMERS
FULL OUTER JOIN ORDERS
ON
CUSTOMERS.CUSTMERID =
ORDERS.CUSTOMERID
ORDER BY CUSTMERS.CUSTOMERNAME;
 
5)      Self-Join:
A self JOIN is a regular join, but the table is joined with itself.
Syntax:
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
Example:
SELECT A.CustomerName AS CustomerName1, B.CustomerName 
B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
A, Customers B
WHERE A.CustomerID <> B.CustomerID
<> B.CustomerID
AND A.City = B.City
= B.City
ORDER BY A.City;

We have the following self-joins, they are
·        Left outer self-join
·        Inner self-join
·        Outer self-join (can be left or right)
·        Cross self-join
 
6)      Cross-Join:
Cross join will not have ‘ON’ clause. When you cross join between two tables
then we get a Cartesian product. Which is nothing but no.of rows in table A is
multiplied by no.of rows in tableB.
 
Note: You can’t use ‘=’ for NULL search in SQL query in SQL server.
 
Ø  Advanced or Intelligent Joins:
or Intelligent Joins:
Ref link: http://technicalstack.com/advance-joins-in-sql-server/
-        Get non-matching records from left table

-         Get non-matching records from right table  
-         Get non-matching records from both tables
 
        

 

Interview Question:

-      You will have Output from one set of query and you want to use this data to see it in table? How can use achieve this?
Ans: Using “Nested SQL”