SQL Practice: Level 18
Hands-On SQL Training
I think it's high time to put everything you've learned in this level into practice - time for a real SQL duel! We believe in you, SQL Padawan! In this assignment you need to write queries and send them to us through the form. You can see the correct output in the test terminal when you select the task number. Your mission is to get the same result using the power of SQL!
Database Schema "Computer Firm"
Cool schema! Besides the structure of the database, you can see the relationships and the key fields that link the tables
1. Find the model number, speed, and hard disk size for all PCs costing less than $500. Output: model, speed, and hd
2. Output all PC makers. No duplicates.
Hint: to eliminate duplicates, add the DISTINCT keyword
3. Find the printer makers, without duplicates. Output: maker
4. Find the model number, memory size, and screen size of laptops priced above $1000.
5. Find all color printer options the company has
6. Find the model number, speed, and hard disk size of PCs that have a 12x or 24x CD and cost less than $600
7. For each maker that produces laptops with a hard disk of at least 10 GB, find the speeds of those laptops. Output: maker, speed, no duplicates.
8. We're looking for a color printer. Output the prices and models of all color printers
9. Output all data for all computers whose CD-ROM speed is above 12.
10. Output the model, price, and type of black-and-white laser printers or color inkjet printers
This site will help you do the practical
assignment
Take the time to really understand how the tables are structured before you start writing queries. Either way, come back to the schema whenever you need it
The database schema consists of four tables:
Product (maker, model, type)
PC (code, model, speed, ram, hd, cd, price)
Laptop (code, model, speed, ram, hd, price, screen)
Printer (code, model, color, type, price)
The Product table holds the maker (maker), the model number (model), and the type ('PC' for a personal computer, 'Laptop' for a laptop, or 'Printer' for a printer).
It is assumed that model numbers in the Product table are unique across all makers and product types.
For each PC, uniquely identified by a unique code – code, the PC table specifies the model – model (a foreign key to the Product table), the speed – speed (of the processor, in megahertz), the memory size – ram (in megabytes), the disk size – hd (in gigabytes), the speed of the reader – cd (for example, '4x'), and the price – price.
The Laptop table is similar to the PC table, except that instead of the CD speed it contains the screen size -screen (in inches).
For each printer model, the Printer table specifies whether it is a color printer – color ('y' if it is color), the printer type – type (laser – 'Laser', inkjet – 'Jet', or dot matrix – 'Matrix'), and the price – price.
Quirks of working with MySQL: the function MAX (pc.price) will return an error, whereas MAX(pc.price) without a space will work fine. Pay attention to quotation marks, closing parentheses, and extra spaces when writing your query.