Lab 2 Single Table SQL Queries Premiere database Construct

Lab 2 – Single Table SQL Queries: Premiere database

Construct the following queries in SQL and execute them in MySQL database you have created (The Premiere database).

Your output for each query must contain the SQL code and the resulting table. You can copy & paste your SQL code and output table below each query question. You can submit your lab report in a MS Word or PDF file via Blackboard. I will also look at your work in class.

a) List the names of customers with credit limits of $7,500 0r less.

b) List the order number for each order placed by customer 148 on 10/23/2007.

‘yyyy-mm-dd’, enclosed in single quotes.

c) Find all customers whose balances are less than their credit limits.

d) How many customers have balances which are less than their credit limits.

e) Find the total of the balances for all customers whose sales rep is 65 and

whose balances are less than their credit limits.  

Solution

a. SELECT * FROM `customer` where credit_limit <= 7500

b.SELECT * FROM `orders` where customer_num=148 AND order_date = STR_TO_DATE(\'23/10/2007\', \'%d/%m/%Y\')

c. SELECT * FROM `customer` where balance < credit_limit

d. SELECT count(*) FROM `customer` where balance < credit_limit

e. SELECT sum(balance) FROM `customer` where rep_num =65 AND balance < credit_limit