Quick Notes on SQLs
Relational Database –
Non-Relational Database –
SQL – Started by IBM
- Retrieving Data from a Single Table
- Select , From , Where , Order By
- Select Distinct to create unique items
- We can use mathematical operators in select e.g. select points, points + 10 etc – this will retrieve point and then add 10 to the points to another column name points.
- Where Clause :
- AND, OR and NOT Operators
- IN Operator – Select * from Customers Where state In (‘VA’, ‘FL’, ‘GA’)
- IN Operator – Select * from Customers Where state Not In (‘VA’, ‘FL’, ‘GA’)
- The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
- The BETWEEN operator is inclusive: begin and end values are included.
- Like operator ‘b%’ or ‘%b%’ or ‘b_____y’ (here dash means number of characters
- Select * from customers where address LIKE ‘%trail%’ OR address LIKE ‘%avenue%’ (Get the customers whose addresses contain Trail or Avenue)
- Get the customers whose phone numbers end with 9
- Select * from customers where phone like ‘%9’
- Select * from customers where last_name REGEXP ‘field$’ (Many search patterns available)
- Get the orders that are not shipped : – Select * from Orders Where shipped_date Is Null
- Order-By Various combinations
- Limit various limits
- Limit 6, 3 ( skip first six records and pick next 3 records) (Limit comes at end)
- Retrieving Data from Multiple Tables
- Inner Joins (JOIN means Inner Join)
- Select * from orders join customers on orders.customer_id = customers.customer_id;
- Select * from order_items join products on order_items;
- Select * from order_items oi join products p on oi.product_id = p.product_id;
- Joining Across Databases :
- Inner Joins (JOIN means Inner Join)
- Self Joins
- Select * from employees e join employees m On e.reports_to = m.employee_id
- Joining multiple tables
Select all customers whose orders got shipped or processed or initiated (means a null entry order may say – this order not processed at all)
- Select * from orders o join customers c
- On o.customer_id = c.customer_id
- Join order_statuses os
- On o.status = os.order_status_id
- Select * from payments p
- Join clients c
- On p.client_id = c.client_id
- Join payment_methods pm
- P.payment_method = pm.payment_method_id
- Join clients c
- Inner join may have compound join conditions to join multiple tables
- Implicit Join syntax –
- Select * From orders o , customers c
- Where o.customer_id = c.customer_id
- If we remove the condition then it will become cross-join
OUTER JOIN
Outer JOIN are two types – Left join and Right Join
- Select c.customer_id, c.first_name,o.order_id From customers o LEFT JOIN customers c ON c.customer_id = o.customer_id Order By C.customer_id ( it will show match and all entries from left table even no match)
- Select c.customer_id, c.first_name,o.order_id From orders o LEFT JOIN customers c ON c.customer_id = o.customer_id Order By C.customer_id ( it will show match and all entries from left table even no match)
- Self Outer Join
- USING
- NATURAL JOIN
- Union – combine result from two queries