SQL

1. Anatomy of a simple SQL statement

• The SELECT keyword instructs SQL to retrieve records; as opposed to updating or deleting them
  • SELECT *means select ‘all columns’
• The <column list> contains a comma separated list of columns (attributes) of interest
• The FROM keyword precedes a comma separated list of tables. If a column is used in the <column list> then the table containing that column must be present in the <table list>
• The WHERE keyword instructs SQL to first filter the records before returning them. A WHERE clause has two main functions:
  • Restricting the number of rows selected by a query to a subset of the total rows
  • Join rows from two or more tables
• The AND keyword is used when there are multiple filters. AND is very common when joining one or more tables together
• The OR keyword is used as a conjunction when there are multiple alternatives

2. Schema in a Database

• A schema is a physical model of what a database looks like
• It shows all the tables and the relationship each table has with other tables in the database

Critical concepts in a database

1. Tables (Entities): The rectangular boxes in the schema are entities. They represent categories of data the database will contain
   • All the attributes (columns) of a table are shown within its box
2. Attributes: An attribute is an item of information which is stored about an entity
3. Primary Key: To implement a relationship between two tables in a relational database. The primary key is mandatory and must always have a value in it
   • In DB schema terminology it must be NOT NULL
   • Primary keys must contain unique values
   • Types of Primary key:
     • Simple / Natural Key:
       • Consists of a single attribute to uniquely identify a record
       • The simple key has a unique meaning to the business or already exists in the real world
       • It has meaning outside the system. E.g. NRIC, Licence Plate Number, Area Code
     • Surrogate Key:
       • Consists of a single attribute to uniquely identify a record
       • Usually defined as INTEGER type and is auto-generated by the system
       • It has no business meaning and has no meaning outside the system
     • Compound Key:
       • A compound consists of more than one attribute to uniquely identify a record
       • Each attribute, which makes up the primary key, is also a simple key in its own right
       • Is a combination of other primary keys and no other column
     • Composite Key:
       • A composite key consists of more than one attribute to uniquely identify a record
       • One or more of the attributes, which make up the key, are not simple keys in their own right
       • Usually formed by adding additional columns to an existing key to get uniqueness
4. Foreign Key: Are used to link a table to another table to form a relationshipbetween them
5. Relationship: The lines between two tables indicates that they have a relationship or are associated
   • One to One
   • One to Many / Many to One (Dependent on which direction the user are reading from)
   • Many to Many

3. String Manipulation Functions

1. CONCAT(str1, str2,…)
   • Return concatenated string. Can accept many parameters
   • E.g. CONCAT(‘My’, ‘SQL’) returns ‘MySQL’
2. INSTR(str,substr)
   • Return the index of the first occurrence of substring
   • E.g. INSTR(‘database’, ‘a’) returns 2
3. LOWER(str)
   • Return the argument in lowercase
4. UPPER(str)
   • Return the argument in uppercase
5. LENGTH(str)
   • Returns the length of the string
6. REPLACE(str,from_str,to_str)
   • Replace occurrences of a specified string.
   • REPLACE() performs a case-sensitive match when searching for from_str.
   • E.g. REPLACE(‘wWw.mysql.com’, ‘w’, ‘m’) returns ‘mWm.mysql.com’
7. SUBSTR(str,pos,[len])
   • Return the substring as specified
   • E.g. SUBSTR(‘MySQL’, 3, 2) returns ‘SQ’

4. SQL Script

SELECT OL.OutletLocation AS "Outlet Name", DATE_FORMAT(D.DeliveryDateAndTime, '%d/%m/%Y %H:%i') AS "Delivery Date and Time",

ORD.OrderNumberID AS "Order Number",

MI.FoodItemDescription AS "Item Not Available", U.UserName AS "Customer Name", U.ContactNumber AS "Contact Number"

FROM ((((((User U

INNER JOIN `Order` ORD ON ORD.UsernameEmail = U.UsernameEmail)

INNER JOIN Delivery D ON D.DeliveryID = ORD.DeliveryID)

INNER JOIN Order_has_MenuItems OM ON OM.OrderNumberID = ORD.OrderNumberID)

INNER JOIN MenuItems MI ON MI.MenuItemID = OM.MenuItemID)

INNER JOIN Outlet_has_MenuItems OLM ON OLM.MenuItemID = MI.MenuItemID)

INNER JOIN Outlet OL ON OL.OutletID = OLM.OutletID)

WHERE ORD.OrderStatusID = 000001

AND OLM.ItemStockQuantity = 0

AND ORD.OutletID = OLM.OutletID

ORDER BY OL.OutletLocation ASC, D.DeliveryDateAndTime, ORD.OrderNumberID, ORD.TimeOfCollection;

SELECT E.EventTitle AS "Event Name", DATE_FORMAT(E.EventStartDateTime, '%d/%m/%Y') AS "Event Date",

COUNT(ESU.EventID) AS "Number of Participants"

FROM (Events E

Left JOIN Events_has_User ESU ON ESU.EventID = E.EventID)

GROUP BY E.EventTitle, E.EventStartDateTime

ORDER BY COUNT(ESU.EventID) DESC;

SELECT OL.OutletLocation AS "Outlet", OL.OutletManager AS "Outlet Manager", SUM(MIHS.UnitPrice * OM.OrderQuantity) AS "Total Sales ($)"

FROM (((((Outlet OL

INNER JOIN Outlet_has_MenuItems OHM ON OHM.OutletID = OL.OutletID)

INNER JOIN MenuItems MI ON MI.MenuItemID = OHM.MenuItemID)

INNER JOIN MenuItems_has_Size MIHS ON MIHS.MenuItemID = MI.MenuItemID)

INNER JOIN Order_has_MenuItems OM ON OM.MenuItemID = MI.MenuItemID)

INNER JOIN `Order` ORD ON ORD.OrderNumberID = OM.OrderNumberID)

WHERE ORD.OrderStatusID = 000004

AND OHM.ItemStockQuantity > OM.OrderQuantity

AND MONTH(ORD.DateOfOrder) = 12

GROUP BY 1, 2

ORDER BY 3 DESC;

SELECT MI.FoodItemDescription AS "Item", SUM(OM.OrderQuantity) AS "Quantity Sold"

FROM ((MenuItems MI

INNER JOIN Order_has_MenuItems OM ON OM.MenuItemID = MI.MenuItemID)

INNER JOIN `Order` ORD ON ORD.OrderNumberID = OM.OrderNumberID)

WHERE OM.OrderQuantity < 10 AND MONTH(ORD.DateOfOrder) = MONTH(CURDATE()) - 1

GROUP BY 1

ORDER BY 2;

SELECT DATE_FORMAT(DateOfOrder, '%M %Y') AS "Month", SUM(MIHS.UnitPrice * OM.OrderQuantity) AS "Total Amount Spent ($)",

CAST(AVG(MIHS.UnitPrice * OM.OrderQuantity) AS DECIMAL (10,2)) AS "Average Amount per Order ($)",

MIN(MIHS.UnitPrice * OM.OrderQuantity) AS "Minimum Amount of an Order ($)",

MAX(MIHS.UnitPrice * OM.OrderQuantity) AS "Maximum Amount of an Order ($)"

FROM (((`Order` ORD

INNER JOIN Order_has_MenuItems OM ON OM.OrderNumberID = ORD.OrderNumberID)

INNER JOIN MenuItems MI ON MI.MenuItemID = OM.MenuItemID)

INNER JOIN MenuItems_has_Size MIHS ON MIHS.MenuItemID = MI.MenuItemID)

WHERE MONTH(ORD.DateOfOrder) BETWEEN 10 AND 12

GROUP BY MONTH(ORD.DateOfOrder);