C.-L. #2 Quiz
• The total mark for this quiz is 100.
• Please refer to the document “Format for Answering an SQL Question” to prepare your answer for Q3.
• Please save your answer sheet as a pdf file for submission. (If you submit a word document, Moodle will convert it to a pdf file; sometimes some content may be distorted.)
Copyright By PowCoder代写 加微信 powcoder
• You do not need to upload your SAS file for Q3.
• You may use SAS Enhancement features in your query for Q3.
Question 1 (25%)
Assume the following two tables are in the ORION database. Table_1
• This homework quiz accounts for 5% of the total marks for this course.
• Your answer sheet is due by the end of the session and should be submitted via
Moodle to the designate link corresponding to your tutorial session. Please submit your answer sheet only (not including the quiz paper).
E_ID DEPT …
T_Date …
E_ID S_ID … …
Please find and list all syntax errors in the following query. Your result should be like the following:
1. Syntax error 1
2. Syntax error 2
n. Syntax error n
Note: your marks will be deducted if your listed error is not an error.
Title ` `title content”;
Select e.e_ID, e.gender, e.DEPT, a. s_ID From Table_1 ‘e’
inner join orion.Table_2 a
Where a.e_ID=e.e_ID
Order by e.DEPT, desc e.e_ID; quit;
Question 2 (35%)
Consider the following three tables in your working folder. Table name: OStaff
Table name: OTransaction
Table name: Oproduct
Answer the following two subquestions:
(a) (5 marks) State what the following query intends to achieve.
(b) (30 marks) Without using SAS EG, show the result table of the following query.
Question 3 (40%)
This question pertains to and the data files customer, customer_type, and order_fact given to you in your lab session. (Note that there are two files: customers and customer. You should use customer in your query.)
Write a query to list the total purchase of all customers who have low activity (see the customer_type column in customer_type table) and use find function in the where clause. Your result table will look like:
Customer ID Customer Type Total Purchase 12322 Orion club members $52,353.00
high activity
Sort your result by customer type and total purchase columns in descending order. Follow the instruction to display your result table. Display only the first and the last 10 rows of your result if the table has more than 20 rows.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com