3.1+SQL out Basics
sql
keyboard_arrow_up
School
University of Saskatchewan *
*We aren’t endorsed by this school
Course
367
Subject
Information Systems
Date
Feb 20, 2024
Type
sql
Pages
3
Uploaded by MagistrateStar1002
/* Title: 3.1 In-class exercise for getting data out of the database
Please use comm311_nsid_comm311_zet605_mycompanydb
*/ # PART I The Basic SELECT Clauses--------------------------------------------------
/*
1. What are the customer names and phone numbers in the customers table? - Display customer name and phone number. */
SELECT phone,customerName
FROM comm311_fij138_mycompanydb.customers;
/*
2. display the customers table */
SELECT *
FROM comm311_fij138_mycompanydb.customers; /*
2. What are customer number, customer name, phone, credit limit and new credit limit (Where each customers credit limit will be increased by 15%) of the customers?
- Display five columns containing customer number, name, phone, credit limit and a new column named New Credit Limit
*/
SELECT customerNumber, customerName, phone, creditLimit,
creditLimit*(1+0.15) AS NewCeditLimit
FROM comm311_fij138_mycompanydb.customers;
/*
3. What is the total number of the states in the customers table?
*/
SELECT count(states)
FROM comm311_fij138_mycompanydb.customers;
/*
4. What is the total number of the distinct states in the customer table?
- Display the total number of distinct states */
SELECT COUNT(DISTINCT city)
FROM comm311_fij138_mycompanydb.customers;
/*
6. What is the the average credit limit of all the customers?
*/
SELECT AVG(creditLimit)
FROM comm311_fij138_mycompanydb.customers;
/*
7. What are the maximum and minimum credit limits in the customer table?
*/
SELECT # PART II adding more clauses-------------------------------------------------- /*
8. select customers who are not from Canada
- display customerNumber and customer names
***Which tables and columns store the required data to build this query?
*/
SELECT /*
9. What are the names of the customers whose creditLimits are greater than 30000 and less than 10000. - Display customer names and their credit limit
*/
SELECT /*
10. What are the names of the customers who are from USA, Canada or France? - Display customer names and their credit limit
*/
SELECT /*
11. Who are the customers whose name starts with 'la'? - Display customer names.
*/
SELECT /* 12. What are the names of the customers whose creditLimits are greater than or equal to 10000 and less than or equals to 30000, and who are from USA, Canada or France?
- Display customer names and their credit limit */
SELECT # extra question
# How many customers have creditLimits greater than or equal to 10000 and less than
or equals to 30000, and are from USA, Canada or France?
## you can choose to customerNumber or simply count(*)
SELECT
/*
13. Who are the customers whose name contains the word 'gift', and who have creditLImit data in the database - display all the columns in the customers table
*/
SELECT # how many customers are there whose name contains the word 'gift', and who have creditLImit data in the database
## you can choose to customerID or simply count(*)
SELECT
/*
14. What are the customer name and customer contact names, sorted by contact last name in ascending order first
# and then by contact first name in a descending order.
- Display customerName, contact last and first name.
*/
SELECT /*
15. what's the average creditLimit of customers for each country? - display the country name and the corresponding average creditLimit
- write a single query statement to answer this question
*/
SELECT # PART III adding all together-------------------------------------------------- /*
16. Which customer in Las Vegas has the highest creditlimt?
- display customer name and creditLimit
*/ SELECT /*
17. which order is the lastest order? Display the order number and order date. Hint: -- the information required for this query is stored in the Orders table
-- （
Ask student to delete this one: "disrupted" is a value in the status column of
the table
*/
SELECT /*
18. find the top 3 countries with the highest average creditLimit?
sort the results by the average creditLimit.
- Display country and average creditLimit
*/
SELECT SELECT
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