Week5_Tutorial_ICT200_T323 (1)

docx

School

Western Sydney University *

*We aren’t endorsed by this school

Course

103

Subject

Information Systems

Date

Dec 6, 2023

Type

docx

Pages

Uploaded by MateMongooseMaster577

T3 2023: ICT200 Database Design and Development Tutorial: Week 5 Topic: Structured Query Language (SQL) – Part 1 Submission: Five minutes before the end of the tutorial. Instructions: Form a group of 2-4 students and complete the following group activities. After the discussion, each student answers the questions individually in their own words. Every student must upload the answer file on Moodle individually. Part A: Video Instructions for using SQL Server Management Studio (SSMS) on your computer https://www.youtube.com/watch?v=moCgS2kfxnU https://www.youtube.com/watch?v=RSlqWnP-Dy8 For the online version of SQL Server: Sign up at https://livesql.oracle.com/ SQL Resources for more practice: https://www.sqlcourse2.com/ MCQs and True/False i. What command is used to create a new table in SQL? a. GENERATE b. BUILD c. CREATE d. INSERT ii. What does the following code snippet do? SELECT * FROM Student WHERE age > 18; a. Displays all students b. Displays all columns with age >18 c. Displays only age column with age >18 d. IN iii. In an SQL query, which SQL keyword is used to determine if a column value is equal to any Questions extracted from: Database Processing: Fundamentals, Design, and Implementation, David M. Kroenke and David Auer , 16 th ed, Global Edition, Pearson ICT200 Tutorial Compiled by: Anupam Makhija ICT103: Tutorial 1 Compiled by: Shaleeza Sohail

one of a set of values? a. AND b. EXISTS c. OR d. IN iv. In what scenario a DML statement will not be executed? a. When existing rows are modified. b. When a table is deleted. c. When some rows are deleted. d. All of the above v. Which of the following is the full form of DDL? a. Data Definition Language b. Data Derivation Language c. Data Detail Language d. Dynamic Data Language vi. The SQL keyword LIKE is used in SQL expressions to select partial string values. True/False vii. A primary key can be composed of one or more attributes True/False Questions extracted from: Database Processing: Fundamentals, Design, and Implementation, David M. Kroenke and David Auer , 16 th ed, Global Edition, Pearson ICT200 Tutorial Compiled by: Anupam Makhija ICT103: Tutorial 1 Compiled by: Shaleeza Sohail

Part B: Choose one of the following processes depending on the version you are using: SQL Server Management Studio: 1. Create a new database called ‘Cape_Codd’ using command: CREATE DATABASE Cape_Codd; 2. Create tables using the script ‘CreateData.sql’ provided in the Database Script folder in week 5 Moodle resources. 3. Populate these tables using the script ‘InsertData.sql’ provided in the Database Script folder in week 5 Moodle resources. 4. Write an SQL statement to display all records from the SKU table using the command: SELECT * FROM SKU_DATA; Online Version 1. Create an account on https://livesql.oracle.com/ and log in to the online version of SQL Server. 2. Create tables using the script CreateData_Online.sql provided in the Database Script folder in week 5 Moodle resources. 3. Populate these tables using the script ‘InsertData_Online.sql’ provided in the Database Script folder in week 5 Moodle resources. SQL Questions: Implement the following SQL statements using SQL Server. Provide screenshots of the output in the answer. i. Display SKU, SKU_Description and Department columns from SKU_Data. ii. Write an SQL statement to display warehouse ID, SKU and SKU_Description and Quantity on Hand. iii. Write an SQL statement to display unique buyers. iv. Write an SQL statement to display top 10 most expensive products from Order_Item table. Sort the results in ascdending order by price. v. Write an SQL statement to display all columns of Order_Item. Sort the results in ascending order by SKU and descending order by Quantity. vi. Write an SQL statement to display SKU, SKU_Description and WarehouseID for products having QuantityOnHand not equal to 0. Questions extracted from: Database Processing: Fundamentals, Design, and Implementation, David M. Kroenke and David Auer , 16 th ed, Global Edition, Pearson ICT200 Tutorial Compiled by: Anupam Makhija ICT103: Tutorial 1 Compiled by: Shaleeza Sohail

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

vii. Write an SQL statement to display SKU and SKU_Description, Department for ‘Climbing’ and ‘Water Sports’ departments. Revise for the quiz and attempt the MCQ Quiz A (5%) on Moodle as per the instructions from your tutor. Questions extracted from: Database Processing: Fundamentals, Design, and Implementation, David M. Kroenke and David Auer , 16 th ed, Global Edition, Pearson ICT200 Tutorial Compiled by: Anupam Makhija ICT103: Tutorial 1 Compiled by: Shaleeza Sohail

Week5_Tutorial_ICT200_T323 (1)

Related Documents