- SQL (Structured Query Language) is a language designed for interacting with relational database management systems (RDBMS), like MySQL, Oracle, Sqlite etc.
- SQL is developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970's.
What is DDL and DML ?
Data Definition Language (DDL) statements are used to define the database structure or schema.
Data Definition Language (DDL) statements are used to define the database structure or schema.
- CREATE : To create objects in the database.
- ALTER : Alters the structure of the database.
- DROP : Delete objects from the database.
- TRUNCATE : Remove all records from a table, including all spaces allocated for the records are removed.
- COMMENT : Add comments to the data dictionary.
- RENAME : Rename an object.
Data Manipulation Language (DML) statements are used for managing data within schema objects.
- SELECT : Retrieve data from the a database.
- INSERT : Insert data into a table
- UPDATE : Updates existing data within a table.
- DELETE : Deletes all records from a table.
- MERGE : UPSERT operation (insert or update).
- CALL : Call a PL/SQL or Java subprogram.
- EXPLAIN PLAN : Explain access path to data.
- LOCK TABLE : Control concurrency.
DCL is short name of Data Control Language which includes commands such as GRANT, and mostly concerned with rights, permissions and other controls of the database system.
- GRANT : Allow users access privileges to database.
- REVOKE : Withdraw users access privileges given by using the GRANT command.
TCL is short name of Transaction Control Language which deals with transaction within a database.
- COMMIT : Commits a Transaction.
- ROLLBACK : Rollback a transaction in case of any error occurs.
- SAVEPOINT : To rollback the transaction making points within groups.
- SET TRANSACTION : Specify characteristics for the transaction.
Relationships : A relationship is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table. Relationships allow relational databases to split and store data in different tables. There are several types of database relationships.
- One to One Relationship :
- In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.
- Here we have a table for customer, we can put the customer address information on a separate table.
- Now we have a relationship between the Customers table and the Addresses table. If each address can belong to only one customer, this relationship is "One to One".
- Here address_id is a "Foreign Key" and it is used for all kinds of database relationships.
- One to Many and Many to One Relationships :
- A one-to-many and many-to-one relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.
- Here customers can make many orders,Orders can contain many items.
- Each customer may have zero, one or multiple orders. But an order can belong to only one customer.
- Many to Many Relationships :
- In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.
- Here each order can contain multiple items. And each item can also be in multiple orders.
- The Items_Orders table has only one purpose, and that is to create a "Many to Many" relationship between the items and the orders.
- Self Referencing Relationships :
- This relationship is used when a table needs to have a relationship with itself. Here we have taken a referral program.
- Customers 102 and 103 were referred by the customer 101.
- One customer might refer zero, one or multiple customers. Each customer can be referred by only one customer, or none at all.
Join Queries :
- To retrieve data from a database that has relationships, we often need to use join queries.
- Here i've taken a table that have 4 customers. Two customers have two orders, one customer has one order, and one customer has no order.
- Inner Join :
- We wanted to get a list of those customers who placed an order and the details of the order they placed.
- This would be a perfect fit for an inner join, since an inner join returns records at the intersection of the two tables.
- Here nikhil, shiva and manikanta placed orders. But nikhil and manikanta placed two orders each.
- Left Join :
- If we wanted to simply append information about orders to our customers table, regardless of whether a customer placed an order or not we would use a left join.
- A left join returns all records from table A and any matching records from table B.
- Note that since there were no matching records for sudheer in orders table, the amount is NULL, which simply means there is no data for this field.
- Here simply adding a “where order_year is NULL” line to our SQL query, it returns a list of all customers who have not placed an order.
- Right Join :
- Right join is a mirror version of the left join and allows to get a list of all orders, appended with customer information.
- Since there were no matching customer records for orders placed in 2016 and 2017, the first_name and last_name fields are NULL in the resulting set.
- By simply adding a “where first_name is NULL” line to our SQL query returns a list of all orders for which we failed to record information about the customers who placed them.