Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search Store documents

The best documents sold by students who completed their studies

Search through all study resources
Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

University Rankings

Discover the best universities in your country according to Docsity users

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

From our blog

Exams and Study
Go to the blog

Project queries for my sql, Exercises of Computer Science

Elmhurst CollegeComputer Science

Database systems management project queries

Typology: Exercises

2021/2022

Uploaded on 07/13/2025

fnu-shamaila-afreen
fnu-shamaila-afreen 🇺🇸

1 document

1 / 4

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
SQL Queries
Due 05/21/2025 by 11:59 pm
200 Points
You will need to run the books.sql script to create the tables needed for this lab. Download it from
Blackboard and run it in MySQL.
Each query in the script file you will create must be numbered (use either -- 1 or # 1 comments for
numbering) and in order. &The SQL for the following exercises can be written using notepad and run
in MySQL. Read each problem carefully and follow the directions as stated.
&
A CLEAN SCRIPT FILE:
&
A script file is meant to be like a program. The file can be run every time the code needs to be
executed without having to retype the code again each time. For this reason it is important that there
are no errors in the code inside the file. You can go back and forth between notepad and MySQL
when creating your script file to check your queries and verify if they work or not, but you do not want
to create your final output file until after you have verified that everything in your script is correct by
running it in its entirety at least once and viewing the output. Once this has been done, you can create
your final output file, by choosing “Query” from the menu and choosing “Execute (ALL or Selection)
to text”
Things to keep in mind:
If you are not sure of the table names in your user schema you can use the following statement
to list them.
&SHOW TABLES;
If you want to know the name of the columns in a particular table you can use the following
command to list them.
&DESC tableName ;
Be sure to review and verify your final output when you are finished. DO NOT assume
anything.
Write queries for each of the stated problems below that will return a result set of data to satisfy the
requirements. When finished, your script file should have a total of 24 queries and your resulting
output file should show both the query and result set for each. Submit both the script file and the
output file as a single zip file to the dropbox.
Queries 1 through 20 are 8 points each and queries 21 through 24 are 10 points each. Each
query is graded both on following directions and correctness.
pf3
pf4

Partial preview of the text

Download Project queries for my sql and more Exercises Computer Science in PDF only on Docsity!

SQL Queries

Due 05/21/2025 by 11:59 pm

200 Points

You will need to run the books.sql script to create the tables needed for this lab. Download it from Blackboard and run it in MySQL. Each query in the script file you will create must be numbered (use either -- 1 or # 1 comments for numbering) and in order. The SQL for the following exercises can be written using notepad and run in MySQL. Read each problem carefully and follow the directions as stated. A CLEAN SCRIPT FILE: A script file is meant to be like a program. The file can be run every time the code needs to be executed without having to retype the code again each time. For this reason it is important that there are no errors in the code inside the file. You can go back and forth between notepad and MySQL when creating your script file to check your queries and verify if they work or not, but you do not want to create your final output file until after you have verified that everything in your script is correct by running it in its entirety at least once and viewing the output. Once this has been done, you can create your final output file, by choosing “Query” from the menu and choosing “Execute (ALL or Selection) to text” Things to keep in mind:  If you are not sure of the table names in your user schema you can use the following statement to list them. SHOW TABLES;  If you want to know the name of the columns in a particular table you can use the following command to list them. DESC tableName ;  Be sure to review and verify your final output when you are finished. DO NOT assume anything. Write queries for each of the stated problems below that will return a result set of data to satisfy the requirements. When finished, your script file should have a total of 24 queries and your resulting output file should show both the query and result set for each. Submit both the script file and the output file as a single zip file to the dropbox. Queries 1 through 20 are 8 points each and queries 21 through 24 are 10 points each. Each query is graded both on following directions and correctness.

  1. Using the BOOKS table, write a query that will list the categories for the books in inventory. List each category only once.
  2. Using the BOOKS table, write a query that will list the title and publisher ID for each book in the table. Use the column heading of ‘Publisher ID’ for the pubid field.
  3. Using the BOOKS table, write a query that will list the book title, retail price, and the amount of markup for each book. The amount of market is the retail price minus the cost. Use the column heading ‘Price Markup’ for the arithmetic expression column.
  4. Using the BOOK_CUSTOMER table, write a query that will list the customer’s first name, last name, and city for those customers living in zip code 31206.
  5. Using the BOOK_ORDER table, write a query that will list everything about the orders placed prior to April 2, 2003.
  6. Using the BOOK_ORDER table, write a query that will list everything about the orders that have not been shipped yet.
  7. Using the BOOK_CUSTOMER table, write a query using the AND and OR operators that will list the customer information for those customers living in either Florida or New Jersey who have not been referred by another customer.
  8. Using the BOOKS table, write a query that will list all information about those books that are not computer books and do not cost more than $30.00 retail.
  9. Using the AUTHOR table, write a query that will list all information about authors whose first name ends with an “A”. Put the results in descending order of last name, then first name.
  10. Using the BOOK_ORDER table, write a query using the > and < operators that will list the orders that were placed between April 1, 2003 and April 4, 2003. Only show the orders for April 2nd^ or 3rd^ in your result set.
  11. Using the BOOK_ORDER table, write a query that will list the orders that were placed between April 2, 2003 and April 5, 2003 including those placed on the 2nd^ and 5th. Use the BETWEEN operator in writing this query.
  12. Using the BOOKS table, write a query that will list the book title, publisher ID, and published date for all books that were published by publisher 4 or after January 1, 2001. Order the results in ascending order by publisher ID and give the publish date and publisher ID columns meaningful titles.
  13. Many organizations use percentage of markup (e.g., profit margin) when analyzing financial data. To determine the percentage of markup for a particular item, simply subtract the cost for the item from the retail price to obtain the dollar amount of profit, and then divide the profit by

all the books per category with an alias of ‘Cost’. Format the cost column using the correct function to show dollars and cents with a dollar sign.

  1. Using the ORDER_ITEMS table, create a query using the correct functions to return the item#, the total number of items ordered with an alias of ‘Total’, the average number of items ordered with an alias of ‘Average’ (since averages are often in decimals, only show two decimal places), the minimum number of items ordered with an alias of ‘Minimum’, and the maximum number of items ordered with an alias of ‘Maximum’. You output results set should have 5 columns and 4 rows of data.
  2. Using the BOOKS table, create a query using the correct functions to return the category name, the total retail of all books per category with a column alias of ‘Total Retail’, and the average retail of all the books per category with an alias of ‘Average Retail’ for all categories with a total retail greater than $40.00. Format the total retail and average retail columns using the correct function to show dollars and cents with a dollar sign.