1L Bootcam| Level 3 AST: SELECT CAST (25.65 AS varchar); --Example 1: Calculate the number of years the employees have been working SELECT FirstName, 2022 - CAST (HireDate AS int) AS ‘Time Working” FROM Employee e; Dates in SQLite: SQLite supports 6 date and time functions. Date and time values can be stored as text ‘or numbers, Date functions include: © DATE(time-value, modifier, modifier, ete.) + returns the date as text in this format YYYY-MM-DD © TIME(time-value, modifier, modifier, ete.) > returns the time as text in this format: HE:MM:SS « DATETIME(time-value, modifier, modifier, ete.) —> returns the date and time as text in their same formats: YY YY-MM-DD HH:MM:SS_ JULIANDAY(time-value, modifier, modifier, etc.) UNIXEPOCH(time-value, modifier, modifier, ete.) —r retums a unix timestamp as an integer, the number of seconds since 1970-01-01 00:00:00 UTC ¢ STRFTIME(format, time-value, modifier, modifier, etc.) —> retums the date formatted according to the format string specified as the first argument © The format string supports several substitutions + %d day of month (00) + %f fractional seconds (SS:SS8) + %H hour (00-24) + %j day of year (001 — 366) + 4S Julian day number fractional + %m month (01-12) + %M minute (00-59) + %s seconds since 1970-01-01 + %S seconds (00 - 59) + Yow day of week (0-6) Sunday =0 * %W week of year (00 - 53) " %Y year (0000 - 9999) Source: Date And Time Functions (sqlite.org) --Compute the current date SELECT DATE(‘now’); --Compute the last day of the current month SELECT DATE(‘now’, ‘start of month’, “+1 month’, ‘-1 day’) AS LastDay; --Compute the date and time given a unix timestamp 1193961488 -> note, it does not work for dates prior to 1970 SELECT DATETIME(1193961488, “unixepoch’);--Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone SELECT DATETIME(1092941466, ‘unixepoch’, ‘localtime’); --Compute the number of days since Canada became a country, the Dominion of Canada SELECT ROUNDUULIANDAY (‘now’) - JULIANDAY(“1867-07-01')) AS CanadaDays; DATEDIFF: --How old were the employees when they were hired? SELECT FirstName | | ~’ || LastName AS Name, (STRFTIME(‘%Y", HireDate) - STRFTIME(‘6Y”, BirthDate)) — STRFTIME(‘%m-%d’, HireDate) < STRFTIME(‘%0m-%ed’, BirthDate)) AS HireAge FROM Employee e ORDER BY BirthDate; --How old are the employees today? SELECT FirstName | |~’ | | LastName AS Name, (STRFTIME(‘%Y’, ‘now") - STRFTIME(“%Y’, BirthDate)) ~ STRFTIME('%m-%d’, ‘now’) < STRFTIME('%m-"d’, BirthDate)) AS AgeNow FROM Employee e ORDER BY BirthDate; --List the 4 youngest employees in the company (2 ways of querying this data) SELECT * FROM Employee e ORDER BY BirthDate DESC LIMIT 4; SELECT FirstName || ‘-" | | LastName AS Name, (STRFTIME(‘%Y", ‘now’) - STRFTIME(‘%Y’, BirthDate)) — STRFTIME('%m-%d’, ‘now’) < STRFTIME('%m-%6d’, BirthDate)) AS AgeNow FROM Employee ORDER BY AgeNow LIMIT 4; Data definition language (DDL): First, connect to the database:Next, set the Store] database as the active connection.[a teanea3: ce pt 5 Oomstorne = | Saas — | a | Let’s explore how to create tables using queries. Right click on Store! db in the database navigator and select SQL Editor and press new SOL script. CREATE TABLE Customers ( CustomerID INTEGER NOT NULL PRIMARY KEY, Name TEXT, Segment TEXT, City TEXT, State TEXT, PostalCode TEXT, Region TEXT); Populate this table using the INSERT INTO command. INSERT INTO Customers (CustomerID, Name, Segment, City,State, PostalCode, Region) VALUES (12520,Claire Gute’, ‘Consumer’, ‘Henderson’, ‘Kentucky’, 42420’, 'South’), (13045,'Darrin Van Hut?, ‘Corporate’ Los Angeles’ ‘California’ /90036'; West’), (20335,'Sean ODonnell' Consumer’, Fort Lauderdale’ Florida'.'33311"/South’), (11710,Brosina Hoffman','Consumer','Los Angeles! California’90032''West'), (10480,'Andrew Allen’ ;Consumer’,'Concord’North Carolina’ '28027'’South’), (15070,'Irene Maddox’ Consumer’ Seattle’ Washington’'98103',"West’), (14815, "Harold Pawlan ', 'Home Office’, 'Fort Worth’ ,' Texas’ , '76106 ',' Central’), (13868, ‘Darrin Van Huff, ‘Corporate’, 'San Diego’, 'California’, 90036, ‘West’);Create Table “Orders” Create a table for Orders CREATE TABLE Orders ( (Order_ID VARCHAR (20) NOT NULL PRIMARY KEY Order_Date DATE NOT NULL Sales DECIMAL (6.2), Quantity INT NOT NULL Cust_ID INT NOT NULL REFERENCES Customer (CustomerID ), Product_ID INT NOT NULL REFERENCES Products (Produet_ ID) yy Populate tables Orders with INSERT INTO command insert data into table Orders INSERT INTO Orders (Ordet_1D,Order_Date, Sales, Quantty,Cust_ID,Produet_1D) ‘VALUES (2019-145317, 13/18/2019, 2638.48, 12520, 'FUR-BO-10001798), (2021-11868, "10/2/2022, 17499.95, 12520, 'FUR-CH-10000454, ¢(2022-140151', '3/23/2022, 1399.96, 13045, 'OFF-LA-10000240" 6, 5, 4 (2022127180, "10/22/2022, 1119.97, 4, 20335, 'FUR-TA-10000577), (2022-16709, ‘11/17/2022, 10499.97, 3, 20335, ‘OFF-ST-10000760, 1B Bs ¢2021-117121, "12/17/2021", 9892.74,” 13, 11710, | 'FUR-FU-10001487), (2019-16904, "972322019," 9449.95, 11710, 'OFF-AR-10002833'), Create Table “Products” CREATE TABLE Products ( Product_ID INTEGER NOT NULL PRIMARY KEY, Category VARCHAR(50) NOT NULL, Sub_Category TEXT NOT NULL); Insert data in the table Products INSERT INTO Products (Product_ID,Category,Sub_Category) ‘VALUES (10001798, Furniture’ Bookcases’; INSERT INTO Products ‘VALUES (10002640,"Technology','Phones’), (10000240,'Office Supplies',"Labels’), (10000577,'Furniture',"Tables'), (10000760,'Office Supplies',Storage'), (10000562,"Technology’,' Phones’); Creating tables involves the use of constraints that typically enforce rules on the data in a table whenever a row is inserted, updated, or deleted. Examples include: not null, unique, primary key, foreign key, check, default, create index.CustomerID INT NOT NULL REFERENCES Customer(Customerld), ProductID INT NOT NULL REFERENCES Products(Productid) UNIQUE (Orderld)); --this table will ensure that the order ID is different for every order we receive from the customer NOT NULL constrai CREATE TABLE Products ( Productld INTEGER NOT NULL PRIMARY KEY, Category VARCHAR(50) NOT NULL, SubCategory TEXT NOT NULL); Databases also need an entity relationship diagram... Highlight the Productid column from the Products table, then drag and drop it to the Productid column in the Orders table. A message box will pop up to verify the attributes to create the relationship between the two tables —> press OK. (@ Deine vital foign bays |Oeine ital fo be [Products fete ie | Onde Unene ey Onder EW Ugur coke (coker Coker ype af Cohen WC Type SuPodatJO WIEGER” © Reckct OTECER | a‘The syntax for creating a table is as follows: CREATE TABLE table_name ( Columni data type constraint, Column2 data type constraint, Column3 data_type constraint); ‘There are 2 ways to define primary keys and foreign keys while defining a table: [CREATE TABLE Orders ( Orderid VARCHAR(20) NOT NULL PRIMARY KEY, OrderDate DATE NOT NULL, Sales DECIMAL (6,2), Quantity INT NOT NULL, CustomerID INT NOT NULL REFERENCES Customers(Customerld), ProductiD INT NOT NULL REFERENCES Products(Productid)); JEREATE TABLE Orders ( Orderld VARCHAR(20) NOT NULL, OrderDate DATE NOT NULL, Sales DECIMAL (6,2), Quantity INT NOT NULL, CustomeriD INT NOT NULL, ProductiD INT NOT NULL PRIMARY KEY (Orderld) FOREIGN KEY (Customerid) REFERENCES Customers(Customerid) FOREIGN KEY (Productid) REFERENCES Products(Productid)); CHECK constraint: CREATE TABLE People ( Customerld INTEGER NOT NULL PRIMARY KEY, Name TEXT, Segment TEXT, City TEXT, State TEXT, PostalCode TEXT, Region TEXT, Age INT, CHECK (Age >= 18)); ~-this table will only add people who are ages 18 or UNIQUE constraint: CREATE TABLE Orders ( Orderld VARCHAR(20) NOT NULL PRIMARY KEY, OrderDate DATE NOT NULL, Sales DECIMAL (6,2), Quantity INT NOT NULL, der into your tableReplicate the same steps for the Customers table and Orders table to create the relationship for the Customerld columns. Altering tables ALTER TABLE Products ADD COLUMN Origin; ALTER TABLE, Products DROP Origin; ALTER TABLE Products RENAME COLUMN SubCategory TO CategoryDese; ALTER TABLE Products DROP CategoryDese; REPLACE command: ‘Syntax of this function: REPLACE(string, pattern, replacement) UPDATE Customers SET Region = REPLACE(Region, ‘South’, ‘USA South’); CASE Clause: --Example I: Return all of the columns from the customer table and order them by city. When there is no city name, order by state instead, otherwise, the values should be ordered by city SELECT * FROM Customers ¢ ORDER BY (CASE WHEN City IS NULL THEN State ELSE City END): --Example 2: Write a query that returns the customer name, segment, and the state from customers that are under the consumer category. Return a column that display the segment and state for the consumers in North Carolina, California, Florida and all others as “Other” SELECT Name, Segment, CASE WHEN Segment = ‘Consumer’ AND State = ‘North Carolina’ THEN ‘Consumer NC” WHEN Segment = ‘Consumer’ AND State = ‘California’ THEN ‘Consumer CA’ WHEN Segment = ‘Consumer’ AND State = ‘Florida’ THEN ‘Consumer FL’ ELSE ‘Others’ END AS “Segment States’ FROM Customers; --Example 3: Suppose we have a sales volume band. Return the orders’ ID from orders that had highvolume between 15,000 and 30,000, The medium volume is between 10,000 and 13,000, and the sales that are not in the range will return as low volume of sales SELECT Order_ID, CASE WHEN Sales 5000 AND Sales <=30000 THEN ‘High Volume” WHEN Sales >=10000 AND Sales <15000 THEN ‘Med Volume” ELSE ‘Low Volume’ END AS ‘Sales Volume’ FROM Orders 0; —-Example 4: Sort the customers from the South by cities in descending order. For customers from the West, sort the city in ascending order. SELECT Name, Segment, Region, City FROM Customers ORDER BY CASE Region WHEN ‘South’ THEN City END DESC, (CASE WHEN Region = ‘West’ THEN City END;