Database Basics

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,

1^st Form: INSERT INTO table_name  VALUES (value1,value2,value3,...);

2^nd 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

• Retrict Access
• Makes complex query easy
• Present different view from same data table

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)