Basics of SQL
SQL is standard language for database. SQL is a
special-purpose programming language designed for managing data held in a
relational database management system (RDBMS), or for stream processing in a
relational data stream management system (RDSMS).
DBMS processes the SQL request and then retrieves the data
from DB and updates the Data in DB
Datatypes – SQL data type is an attribute that specifies
type of data of any object. Each column, variable and expression has related
data type in SQL.
Char,Varchar2, Number, Date etc
DDL – Data
Definition Language: create, alter, drop, truncate
DML – Data
Manipulation Language: select, insert, delete, update
DCL – Data
Control Language: Grant, revoke
TCL – Transaction
Control Language: commit, rollback
RDBMS Concepts - RDBMS stands for Relational Database
Management System. RDBMS is the basis for SQL, and for all modern database
systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. A
Relational database management system (RDBMS) is a database management system
(DBMS) that is based on the relational model as introduced by E. F. Codd.
Table: The data in RDBMS is stored in database objects
called tables. The table is a collection of related data entries and it
consists of columns and rows
Field: Every table is broken up into smaller entities called
fields, A field is a column in a table that is designed to maintain specific
information about every record in the table
Row: A record, also called a row of data, is each individual
entry that exists in a table. A record is a horizontal entity in a table
NULL – missing/unknown/inapplicable data, its not datavalue,
it indicates value is not known, A NULL value in a table is a value in a field
that appears to be blank, which means a field with a NULL value is a field with
no value. It is very important to understand that a NULL value is different
than a zero value or a field that contains spaces. A field with a NULL value is
one that has been left blank during record creation
Constraints: Constraints are the rules enforced on data
columns on table. These 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 database.
Constraints could be column level or table level. Column level constraints are
applied only to one column whereas table level constraints are applied to the
whole table
Types of
Constraints:
Not Null,
Default, Unique, Primary key, Foreign Key, Check
Data
Integrity:
The
following categories of the data integrity exist with each RDBMS:
Entity
Integrity: There are no duplicate rows in a table.
Domain
Integrity: Enforces valid entries for a given column by restricting the type,
the format, or the range of values.
Referential
integrity: Rows cannot be deleted, which are used by other records.
User-Defined
Integrity: Enforces some specific business rules that do not fall into entity,
domain or referential integrity.
SQL - Syntax:
SQL is
followed by unique set of rules and guidelines called Syntax. All the SQL
statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE,
ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).
Important
point to be noted is that SQL is case insensitive, which means SELECT and
select have same meaning in SQL statements, but MySQL makes difference in table
names.
Create Table – used to create tables in databases
CREATE TABLE
table_name(
column1
datatype,
column2
datatype,
column3
datatype,
.....
columnN
datatype,
PRIMARY KEY(
one or more columns )
);
Alter - Can be used for rename, add, modify or drop columns
in an existing table
ALTER TABLE
table_name
ADD
column_name datatype;
ALTER TABLE
table_name
DROP column_name
datatype;
ALTER TABLE
table_name
ALTER
column_name datatype;
Drop
Rename – Can rename the table name
RENAME TABLE {tbl_name} TO {new_tbl_name};
Truncate – removes all rows from table and also releases
storage space used by table
TRUNCATE TABLE table_name;
Delete – used to delete the data from table and also can
have where condition,
DELETE FROM
table_name WHERE some_column=some_value;
OR
DELETE *
FROM table_name;
OR
DELETE FROM
table_name;
Difference between Delete and Truncate
Data can be recoved
DML/DDL stmt
Release memory
Retrieval of records
Select – Used to retrieve data from the specified table
SELECT
column1, column2....columnN
FROM table_name;
Or
SELECT * FROM
table_name;
Select with DISTINCT Clause – Used to retrieve distinct data
from the table
SELECT
DISTINCT column1, column2....columnN
FROM table_name;
Select with
WHERE Clause – used to retrieve data from table depending on user condition
SELECT
column1, column2....columnN
FROM table_name
WHERE CONDITION;
Select with
AND/OR Clause – Used to retrieve data from table using multiple conditions
SELECT
column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
Select with
IN Clause - Used to specify multiple values in where clause
SELECT
column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);
Select with
BETWEEN Clause - used to select values within a user sepcified range
SELECT
column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
Select with
LIKE Clause - Used in where clause to search for a specified pattern
SELECT
column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
Select with
COUNT Clause - used to count of the rows that matches specified criteria
SELECT
COUNT(column_name)
FROM table_name
WHERE CONDITION;
Select with
ORDER BY Clause - used with select statement to sort the result set
SELECT
column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name
{ASC|DESC};
Select with GROUP BY Clause - Used with select statement to
grouped the selection and aggregate functions are used in selection
SELECT
SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY
column_name;
Select with
HAVING Clause - Having Clause used along with Group By Clause where aggregate
functions are used for filtering the selection
SELECT
SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY
column_name
HAVING
(arithematic function condition);
Alias name – Used to assign a temporary column/table name
eg: select empno AS ”Employeeid“ from emp
All – Used to retrieve all the data from a column from a
table eg: select ALL custname from emp
Relational operators - >,>=,<,<=,!=,,<>
Insert into
– Used to insert new rows into table with null values, and also from different
tables,
1st
Form: INSERT INTO table_name VALUES
(value1,value2,value3,...);
2nd
Form: INSERT INTO table_name (column1,column2,column3,...) VALUES
(value1,value2,value3,...);
Update
syntax – Used to update the data in table with or without where conditions
UPDATE table_name
SET column1=value1, column2=value2,...
WHERE some_column=some_value;
Aggregate functions – aggregate functions return a single
value, calculated from values in a column, useful aggregate functions are as
below
AVG() -
Returns the average value, syntax: SELECT AVG(column_name) FROM table_name
COUNT() -
Returns the number of rows, syntax: SELECT COUNT(column_name) FROM table_name;
FIRST() -
Returns the first row of the table, syntax: SELECT FIRST(column_name) FROM
table_name;
LAST() -
Returns the last row of the table, syntax: SELECT LAST(column_name) FROM
table_name;
MAX() -
Returns the largest value in a column, syntax: SELECT MAX(column_name) FROM
table_name;
MIN() -
Returns the smallest value in a column, syntax: SELECT MIN(column_name) FROM
table_name;
SUM() -
Returns the sum of all the numeric values in a column, syntax: SELECT
SUM(column_name) FROM table_name;
Scalar
Functions - scalar functions return a single value, based on the input value,
useful scalar functions are as below
UCASE() -
Converts a field to upper case, syntax: SELECT UCASE(column_name) FROM
table_name;
LCASE() -
Converts a field to lower case, syntax: SELECT LCASE(column_name) FROM
table_name;
MID() -
Extract characters from a text field, syntax: SELECT
MID(column_name,start,length) AS some_name FROM table_name;
LEN() -
Returns the length of a text field, syntax: SELECT LEN(column_name) FROM
table_name;
ROUND() -
Rounds a numeric field to the number of decimals specified, syntax: SELECT
ROUND(column_name,decimals) FROM table_name;
NOW() -
Returns the current system date and time, syntax: SELECT NOW() FROM table_name;
FORMAT() -
Formats how a field is to be displayed, syntax: SELECT
FORMAT(column_name,format) FROM table_name;
Joins - The SQL Joins clause is used to combine records from
two or more tables in a database. A JOIN is a means for combining fields from
two tables by using values common to each
Types of joins:
INNER JOIN: An
SQL INNER JOIN returns all rows from multiple tables where the join condition
is met. Also known as equijoin
Syntax: SELECT
column_name(s)
FROM table1
INNER JOIN table2
ON
table1.column_name=table2.column_name;
OR
SELECT column_name(s)
FROM table1
JOIN table2
ON
table1.column_name=table2.column_name;
Returns all
rows for matching rows from both the tables
LEFT JOIN:
Return all rows from the left table, and the matched rows from the right table
Syntax: SELECT
column_name(s)
FROM
table1
LEFT
JOIN table2
ON
table1.column_name=table2.column_name;
OR
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON
table1.column_name=table2.column_name;
Note: we can
use “A= B(+)” on columns using in where condition for Left Outer Join (in
Oracle)
RIGHT JOIN:
Return all rows from the right table, and the matched rows from the left table
Syntax: SELECT
column_name(s)
FROM
table1
RIGHT
JOIN table2
ON
table1.column_name=table2.column_name;
OR
SELECT column_name(s)
FROM
table1
RIGHT
OUTER JOIN table2
ON
table1.column_name=table2.column_name;
Note: we can
use “A(+)= B” on columns using in where condition for Right Outer Join(in
Oracle)
FULL JOIN:
Return all rows from both the tables along with the matching records
Syntax: SELECT column_name(s)
FROM
table1
FULL
OUTER JOIN table2
ON
table1.column_name=table2.column_name;
SELF JOIN:
is used to join a table to itself as if the table were two tables, temporarily
renaming at least one table in the SQL statement.
Syntax: SELECT
a.column_name, b.column_name
FROM table1 a, table1 b
WHERE
a.common_field = b.common_field;
CARTESIAN
JOIN: returns the Cartesian product of the sets of records from the two or more
joined tables.
Syntax:
SELECT table1.column1, table2.column2
FROM table1, table2 [, table3 ]
SUBQuery: A Subquery or Inner query or Nested query is a
query within another SQL query and embedded within the WHERE clause. A subquery
is used to return data that will be used in the main query as a condition to
further restrict the data to be retrieved. Subqueries can be used with the
SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =,
<, >, >=, <=, IN, BETWEEN etc.
There are a
few rules that subqueries must follow:
Subqueries
must be enclosed within parentheses.
A subquery
can have only one column in the SELECT clause, unless multiple columns are in
the main query for the subquery to compare its selected columns.
An ORDER BY
cannot be used in a subquery, although the main query can use an ORDER BY. The
GROUP BY can be used to perform the same function as the ORDER BY in a
subquery.
Subqueries
that return more than one row can only be used with multiple value operators,
such as the IN operator.
The SELECT
list cannot include any references to values that evaluate to a BLOB, ARRAY,
CLOB, or NCLOB.
A subquery
cannot be immediately enclosed in a set function.
The BETWEEN
operator cannot be used with a subquery; however, the BETWEEN operator can be
used within the subquery.
Syntax:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT
column_name [, column_name ]
FROM
table1 [, table2 ]
[WHERE])
Independent Subquery – executed first, inner Query is not
dependent on Outer query
e..g display all cut details having loan amount < cust id
= 102
can be used with IN
Correlated subqueries – inner query executed for each n
every row, Outer query needs to be executed before inner query. One of the most
common example of correlated subquery is using keywords exits and not exits
e.g select * from cust_Fd where amount_doller < (Select
max(amount_doller) from cust_loan whereloan_custid = cust_fd.custid)
Exists/Non Exists
Exists – returns true if subquery returns atleast one row
Non Exists – returns false if subquery returns 0 rows
Views – logical representation of subset of data from one or
more tables, view is a virtual table
Create View - a view is a virtual table 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. We can add SQL functions, WHERE, and JOIN statements to a view and
present the data as if the data were coming from one single table.
Syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM
table_name
WHERE
condition
Updating a View - We can update a view by using the
following syntax
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM
table_name
WHERE
condition;
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM
table_name
WHERE
condition
WITH CHECK OPTION; -- With Check
Option used if user tries to insert or delete the data from the view.
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM
table_name
WHERE
condition
WITH READ ONLY; -- With Read
Only used and user tries to insert or delete the data from the view then system
won’t allow.
Complex View – Query with group functions, from multiple
tables
Drop view – Used to drop the view created
Syntax: DROP VIEW view_name
Why View
Index – Speed up the retrival, automatically gets created for
primary and unique
Manually can be done create index index_name on emp(lastname)
create index index_name on emp(upper(lastname))
Drop index index_name
Not to use index whn it is constantly updated
With lot of null values
Poor distribution of data
Having repeated values like Yes/No, Male/Female
etc
SELECT max(salary) FROM emptable WHERE salary < (SELECT
max(salary) FROM emptable);
select max(Emp_Sal) from Employee a where 1 = ( select
count(*) from Employee b where b.Emp_Sal > a.Emp_Sal)
Set operations - Set operators are used to join the results
of two (or more) SELECT statements.The SET operators available are UNION,UNION
ALL,INTERSECT,and MINUS.
Intersect – As name suggest common data available in two
tables will be displayed, common parts
Syntax:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
Minus(Oracle)/Except(sql server) - The SQL MINUS operator is
used to return all rows in the first SELECT statement that are not returned by
the second SELECT statement. Each SELECT statement will define a dataset. The
MINUS operator will retrieve all records from the first dataset and then remove
from the results all records from the second dataset.
Syntax:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
MINUS
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
Union – The SQL UNION operator is used to combine the result
sets of 2 or more SELECT statements. It removes duplicate rows between the
various SELECT statements.
Syntax:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
Union ALL - The SQL UNION operator is used to combine the
result sets of 2 or more SELECT statements.
Syntax:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION ALL
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
What is the
difference between UNION and UNION ALL?
•UNION
removes duplicate rows.
•UNION ALL
does not remove duplicate rows.
Case Select - The SQL Server (Transact-SQL) CASE statement
has the functionality of an IF-THEN-ELSE statement. You can use the CASE
statement within a SQL statement.
Syntax: CASE
expression
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
...
WHEN
value_n THEN result_n
ELSE
result
END
OR
CASE
WHEN condition_1 THEN result_1
WHEN
condition_2 THEN result_2
...
WHEN
condition_n THEN result_n
ELSE
result
END
ISNULL/NVL – check null n replace, Replaces NULL with the specified replacement
value.
Syntax: ISNULL (
check_expression , replacement_value )
Rank – Returns
the rank of each row within the partition of a result set. The rank of a row is
one plus the number of ranks that come before the row in question
Syntax: RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )
Convert - The CONVERT() function is a general function that
converts an expression of one data type to another.The CONVERT() function can
be used to display date/time data in different formats.
Syntax: CONVERT(data_type(length),expression,style)
Eg: CONVERT(VARCHAR(10),GETDATE(),10)
