1. What is the difference between a PRIMARY KEY and a FOREIGN KEY?

a. Primary key cannot be a date variable whereas foreign key can be

b. Primary key can store null value, whereas a foreign key cannot store null
value
c. We can have only one primary key in a table while we can have multiple
foreign keys
d. None of the answers are correct

2. This query is equal to…..

SELECT *
FROM student JOIN takes USING ID);

3. Which table's data will be shown in the result set even if there is no
matching record in table3 from the query below?
SELECT *
FROM table1 t1
INNER JOIN table2 t2 ON t2.id = t1.id
LEFT JOIN table3 t3 ON t3.id = t2.id
a. table1 and table2
b. only table3
c. only table1
d. None of the above

4. Which of the following is/are the correct match(es) between the string function
and its syntax?
 a. STRPOS(value1, value2)
b. All answers are correct
c. TRIM(string, number of characters)
d. RIGHT(value1, value2)

5. What is the purpose of the DISTINCT keyword in SQL?

a. It sorts the result set in ascending order.
b. It performs mathematical calculations on the columns.
c. It removes duplicate rows from the result set.
d. It filters the result set based on a specific condition.

6. Write an SQL query to retrieve the names of customers who live in the city
'New York' from the "Customers" table
a. SELECT names FROM Customers WHERE city = 'New York';
b. SELECT name FROM Customers WHERE city = 'New York';
c. SELECT names FROM Customers HAVING city = 'New York';
d. PICK names FROM Customers WHERE city = 'New York';

7. In an SQL query, which function is used to calculate the average value of a

column?
a. MEAN()
b. SUM()
c. AVERAGE()
d. AVG()

8. You want to merge data from two tables, Orders and Customers, based on a
common CustomerID. Which SQL query would you use?
a. CONNECT Orders TO Customers USING CustomerID;
b. SELECT * FROM Orders INNER JOIN Customers ON
Orders.CustomerID = Customers.CustomerID;
c. MERGE Orders, Customers ON Orders.CustomerID =
Customers.CustomerID;
d. JOIN Orders, Customers ON Orders.CustomerID =
Customers.CustomerID;

9. Which SQL keyword is used to sort the result set in order from the lowest to
the highest?
a. DESC
b. SORT
c. ASC
d. ORDER BY
10. With SQL, what is the syntax to get the name of the product that is called
"Biscuit" in the column named "ProductName" under the table called
"Transaction"?
a. SELECT ProductName FROM ProductName Where ProductName =
“Biscuit”
b. SELECT ProductName FROM Transaction Where ProductName is
“Biscuit”
c. SELECT ProductName Where ProductName is “Biscuit” FROM
Transaction
d. SELECT ProductName FROM Transaction Where ProductName =
“Biscuit”

11. With SQL, how can you insert "Nutella" as the "ProductName" in the "Product"
table?*
a. INSERT INTO Product (ProductName) VALUES ('Nutella')
b. INSERT VALUES ('Nutella') INTO Product (ProductName)
c. INSERT INTO Product ('Nutella') INTO ProductName
d. INSERT ('Nutella') INTO Product (ProductName)

12. With SQL, how can you delete the records where the "FirstName" is
"Madonna" in the Customer Table?
a. DELETE FROM Customer WHERE FirstName='Madonna'
b. DELETE ROW FirstName='Madonna' FROM Customer
c. DELETE FirstName='Madonna' FROM Customer
d. DELETE WHERE FirstName='Madonna' FROM Customer

13. What is/are the advantages of using CTE?

a. After a CTE is defined, it can be used as a Table or a View and can
SELECT, INSERT, UPDATE or DELETE Data.
b. Using CTE improves readability and enables easy maintenance of
complex queries.
c. All answers above are correct
d. The query can be divided into separate, simple, and logical building
blocks, which can be then used to build more complex CTEs until the final
result set is generated.

14. Which of the following date parts can be used with the DATE_DIFF function?
a. HOUR, MINUTE, SECOND
b. DAY, HOUR, MINUTE
c. YEAR, MONTH, DAY
d. WEEK, QUARTER, BI-MONTH
15.

a. The having clause checks the balance of 10000 customer

b. The having clause only count number of users that have total balance
more than 10000
c. The having clause does not check for any condition
d. The having clause checks whether the query result is true or not

16. Write an SQL query to retrieve the names of all employees who earn more
than $50000 from the "Employees" table
a. GET employee_names FROM Employees WHERE salary > 50000;
b. SELECT name FROM Employees HAVING salary > 50000;
c. SELECT name FROM Employees WHERE salary > 50000;
d. EXTRACT name FROM Employees WHERE salary > 50000;

17. With SQL, how do we select all columns from the table "Persons"?
a. SELECT [All] FROM Persons
b. SELECT * FROM Persons
c. SELECT [All Columns] FROM Persons
d. SELECT *.Persons FROM Persons

18. With SQL, how do we get "Artist" and "Song" in the table called "Musician"?
a. SELECT Artist.Musician, Song.Musician FROM Musician
b. SELECT Artist, Song FROM Musician
c. SELECT Musician FROM Artist, Song
d. SELECT Song, Musician FROM Musician

19. How can you change "Alvin" to "Calvin" in the "FirstName" column in the
Subscriber table?
a. UPDATE Subscriber SET FirstName='Alvin' INTO FirstName='Calvin'
b. MODIFY Subscriber SET FirstName='Calvin' INTO FirstName='Alvin'
c. MODIFY Subscriber SET FirstName='Alvin' WHERE FirstName='Calvin'
d. UPDATE Subscriber SET FirstName='Calvin' WHERE FirstName='Alvin'

20. With SQL, how can you return the number of records in the "Transaction"
table?
a. SELECT NO(*) FROM Transaction
b. SELECT COUNT(*) FROM Transaction
c. SELECT LEN(*) FROM Transaction
d. SELECT COLUMNS(*) FROM Transaction
 21. In order to understand the demographic information of users, such as age,
gender, and location, which table would you need to answer this question
based on the provided ERD?
a. Products
b. Users
c. Orders
d. Order_items

22. In terms of the supply of items, which product category has the least
variety of SKU that was input to the system in the year 2019?
a. Clothing Sets
b. Intimates
c. Jeans
d. Jumpsuits & Rompers

23. Please input your query to get the result from the previous question

24. Considering completed orders and focusing on the month of shipment,

which month in the year 2021 had the highest total order performance for
the Jeans category?

a. March
b. December
c. May
d. January

25. Please input your query to get the result from the previous number

26. Which distribution center has the most of the product quantity in Dec 22?

a. Savannah, GA
b. Los Angeles, CA
c. Houston, TX
d. Memphis, TN

27. Please input your query to get the result from the previous number

28. Using the completed orders that were shipped in the year 2021 and
considering the following age group standards:

• 17 and below
• 18 to 24
• 25 to 34
• 35 to 54
• 55 and above

Can you identify the top 1 combination of age group, gender, and country that
contributed the highest number of buyers in 2021? How much percentage
contribute to all buyers?

⚠️ Please be cautious that columns with the same name may not be directly related,
please refer back to the ERD to verify the relation between tables.
a. 25_to_34, F, United States, 7%
b. 25_to_34, F, Russia, 9%
c. 35_to_54, M, China, 5%
d. 55_and_above, M, Indonesia, 5%
2 Please input your query to get the result from the previous number

ag question: Spacer
Business Background
Imagine that you are currently hired as a Data Analyst in the data analytics division
at an eCommerce clothing site company named TheLook that has a job description
to provide insights about product, business, and marketing to relevant stakeholders so
that the business can be sustained (growing and profitable).
Hence, your users (Sheila) have emailed you to understand the business's current
performance (whether the business is growing, steady, or dropping). In addition to
that, she also wants to know what is the key drivers' breakdown per variables causing
the drop/growth of the business to adjust the new strategy that can boost the business
performance in the future.
Data Dictionary TheLook is a fictitious eCommerce clothing site developed by the
Looker team. The dataset contains information about customers, products, orders,
logistics, web events, and digital marketing campaigns. The contents of this dataset
are synthetic and are provided to industry practitioners for product discovery, testing,
and evaluation.
Please go to BigQuery and add the dataset with tutorialLinks to an external site.

⚠️ The dataset is created specifically for this assignment. Do not use another source
of dataset other than from the tutorial above
To help you understand the table / schema better, refer to this DATA
DICTIONARYLinks to an external site.
You can use this ERD Links to an external site.to answer the questions