SQL
SQL stands for Structured Query Language.
RDBMS stands for Relational Database Management System.
DDL - Data Definition Language
CREATE- Creates a new table, a view of a table, or other object in the database1
2
3
4
5
6
7
8CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
...
columnN datatype,
PRIMARY KEY (one or more columns)
);ALTER- Modifies an existing database object, such as a table1
2
3
4
5ALTER TABLE table_name
{ADD|DROP|MODIFY} column_name {datatype};
ALTER TABLE employees
DROP CONSTRAINT employees_pk;DROP- Deletes an entire table, a view of a table or other objects in the database1
DROP TABLE table_name
DML - Data Manipulation Language
SELECT- Retrieves certain records from one or more tables1
2
3
4
5SELECT column1, column2, ...columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2
ORDER BY column_name {ASC|DESC}
HAVING (arithematic function condition);INSERT- Creates a record1
2INSERT INTO table_name (column1, column2, ...columnN)
VALUES (value1, value2, ...valueN);UPDATE- Modifies records1
2
3UPDATE table_name
SET column1 = value1, column2 = value2, ...columnN = valueN
[WHERE CONDITION];DELETE- Deletes records1
2DELETE FROM table_name
WHERE {CONDITION}
DCL - Data Control Language
GRANT- Gives a privilege to userREVOKETakes back privileges granted from user
Other syntaxes
LIKE%represents zero, one or multiple characters_represents a single number or character.
ORDER BY1
2
3
4
5
6
7
8SELECT * FROM customers
ORDER BY (CASE address
WHEN 'DELHI' THEN 1
WHEN 'BHOPAL' THEN 2
WHEN 'KOTA' THEN 3
WHEN 'AHMADABAD' THEN 4
WHEN 'MP' THEN 5
ELSE 100 END) ASC, ADDRESS DESC;First sort in your own order, then order the rest by address in reverse alphabetical order
JOIN- a means for combining fields from two tables by using values common to each1
2
3
4
5
6
7
8SELECT id, name, amount, date
FROM customers
JOIN orders
ON customers.id = orders.customer_id;
SELECT id, name, age, amout
FROM customers, orders
WHERE customers.id = orders.customer_id;
Constraints
NOT NULL- Ensures that a column cannot have a NULL valueDEFAULT- Provides a default value for a column when none is specifiedUNIQUE- Ensures that all the values in a column are differentPRIMARY KEY- Uniquely identifies each row in a database tableFOREIGN KEY- Uniquely identifies a row in any other database tableCHECK- Ensures that all values in a column satisfy certain conditionsINDEX- Used to create and retrieve data from the database very quickly
Data Integrity
- 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
Database Normalization
Why?
- Eliminating redundant data, for example, storing the same data in more than one table
- Ensuring data dependencies make sense
Guidelines
- First Normal Form
- Second Normal Form
- Third Normal Form
Transactions
Properties
- Atomicity - Ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
- Consistency - Ensures that the database properly changes states upon a successfully committed transaction
- Isolation - Enables transactions to operate independently of and transparent to each other
- Durability - Ensures that the result or effect of a committed transaction persists in case of a system failure
Control
COMMIT - to save the changes
- Only used with the DML Commands such as
INSERT,UPDATEandDELETE
- Only used with the DML Commands such as
ROLLBACK - to roll back the changes
SAVEPOINT - creates points within the groups of transactions in which to ROLLBACK
1
2
3SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;
SET TRANSACTION - places a name on a transaction