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 :
• 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

• 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  

• Database Technologies