DBS211 Lab 03 – SQL – Single Table Queries solved

$30.00

Category: You will Instantly receive a download link for .zip solution file upon Payment || To Order Original Work Click Custom Order?

Description

5/5 - (1 vote)

Objectives

The purpose of this lab is to start learning SQL by writing basic DML statements involving a single table.  You will learn to create basic CRUD statements (queries as well as insert, update and delete).

 

Submission:

Your submission will be a single Word document with the question#, SQL query, solutions if it is a text or a screen shot provided. (with a .docx file extension). Create a comment header that includes your name, student id, the date and the purpose of the file

(i.e. DBS211 – Lab 03_firstname).

 

Setup

Create a new worksheet in SQL developer and add an appropriate comment header that includes your name, student id, the date and the purpose of the file (i.e. DBS211 – Lab 03).   After every command and result in each question paste them in your Word file

Immediately under the comment header, enter the following line and then execute it:

SET AUTOCOMMIT ON;

 

Style Guide

Your SQL should be written using the standard coding style:

  • all keywords are to be upper case,
  • all user-defined names are to be lower case, (example: table and field names)
  • there should be a carriage return before each major part of the SQL statements (i.e. before SELECT, FROM, WHERE and ORDER BY)

Using comments to number the question answers, write the SQL code to complete the following tasks.

 

Tasks:

  1. Display the first 10 rows of data for the RETAILPAYMENTS table. (query and results in Word file).
  2. Display the full name of RETAILEMPLOYEE (in 2 ways) and their email using the RETAILEMPLOYEEs table whose office code is 6.
  3. Display RETAILCUSTOMER number, RETAILCUSTOMER name, contact first name and contact last name, and phone for all RETAILCUSTOMERs in Paris. (hint: be wary of case sensitivity)
  4. Repeat the previous Query with a couple of small changes:
  5. The contact’s first and last name should be in a single column in the format “lastname, firstname”.
  6. Show RETAILCUSTOMERs who are in Canada

 

  1. Display RETAILCUSTOMER number for RETAILCUSTOMERs who have payments.  Do not included any repeated  (hints: how do you know a RETAILCUSTOMER has made a payment? You will need to access only one table for this query)
  2. List RETAILCUSTOMER numbers, check number, and amount for RETAILCUSTOMERs whose payment amount is not in the range of $30,000 to $65,000. Sort the output by top payments amount first.
  3. Display the order information for all RETAILORDERS that are cancelled.
  4. The company needs to know the percentage markup for each RETAILPRODUCT sold.  Produce a query that outputs the ProductCode, ProductName, BuyPrice, MSRP in addition to
  5. The difference between MSRP and BuyPrice (i.e. MSRP-BuyPrice) called markup
  6. The percentage markup (100 * calculated by difference / BuyPrice) called percmarkup
    rounded to 1 decimal place.
  7. Display the information of all RETAILPRODUCTs with string ‘co’in their product name. (c and o can be lower or upper case).
  8. Display all RETAILCUSTOMERs whose contact first name starts with letter s(both lowercase and uppercase) and includes letter e(both lowercase and uppercase).