SQL Notes

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  

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s