CS: SQL: SELECT, WHERE, JOIN
Querying tables, filtering rows, and combining data
CS: SQL: SELECT, WHERE, JOIN
Querying tables, filtering rows, and combining data
CS - Grade 9-12
- 1
A table named Students has columns student_id, first_name, last_name, and grade_level. Write a SQL query that shows only the first_name and last_name columns for every student.
Use SELECT to choose columns and FROM to choose the table.
The query is: SELECT first_name, last_name FROM Students; This selects only the first_name and last_name columns from the Students table. - 2
A table named Books has columns book_id, title, author, and pages. Write a SQL query that shows all columns for books with more than 300 pages.
The query is: SELECT * FROM Books WHERE pages > 300; This returns every column for rows where the pages value is greater than 300. - 3
A table named Movies has columns movie_id, title, rating, and release_year. Write a SQL query that shows the title and release_year of movies with a rating equal to 'PG-13'.
Text values in SQL are usually written in single quotes.
The query is: SELECT title, release_year FROM Movies WHERE rating = 'PG-13'; This returns the title and release_year for movies whose rating is PG-13. - 4
Use this table: Employees(employee_id, first_name, last_name, department, salary). Write a SQL query that shows all employees in the 'Sales' department who have a salary greater than 50000.
The query is: SELECT * FROM Employees WHERE department = 'Sales' AND salary > 50000; This filters the table to employees who are in Sales and earn more than 50000. - 5
A table named Products has columns product_id, product_name, category, and price. Write a SQL query that shows product_name and price for products in the 'Electronics' category with a price less than or equal to 100.
Use AND when both conditions must be true.
The query is: SELECT product_name, price FROM Products WHERE category = 'Electronics' AND price <= 100; This returns the name and price for Electronics products priced at 100 or less. - 6
Two tables are shown. Customers has columns customer_id, name, and city. Orders has columns order_id, customer_id, and order_date. Write a SQL query that shows each order_id with the customer's name.
Join the tables using the column they have in common.
The query is: SELECT Orders.order_id, Customers.name FROM Orders JOIN Customers ON Orders.customer_id = Customers.customer_id; This joins orders to the matching customer and returns the order ID with the customer's name. - 7
Two tables are shown. Authors has columns author_id and author_name. Books has columns book_id, title, and author_id. Write a SQL query that shows each book title with its author_name.
The query is: SELECT Books.title, Authors.author_name FROM Books JOIN Authors ON Books.author_id = Authors.author_id; This combines each book with the author whose author_id matches. - 8
Use these tables: Students(student_id, name) and Enrollments(enrollment_id, student_id, course_name). Write a SQL query that shows the names of students enrolled in 'Biology'.
The WHERE clause should come after the JOIN condition.
The query is: SELECT Students.name FROM Students JOIN Enrollments ON Students.student_id = Enrollments.student_id WHERE Enrollments.course_name = 'Biology'; This joins students to their enrollments and filters for Biology. - 9
Use these tables: Teams(team_id, team_name) and Players(player_id, player_name, team_id). Write a SQL query that shows player_name and team_name for every player.
The query is: SELECT Players.player_name, Teams.team_name FROM Players JOIN Teams ON Players.team_id = Teams.team_id; This returns each player with the team name from the matching team row. - 10
A table named Sales has columns sale_id, item_name, quantity, and store_location. Write a SQL query that shows item_name and quantity for sales where store_location is 'Denver' or 'Austin'.
Use OR when either condition can be true.
The query is: SELECT item_name, quantity FROM Sales WHERE store_location = 'Denver' OR store_location = 'Austin'; This returns sales from either Denver or Austin. - 11
Use these tables: Courses(course_id, course_name, teacher_id) and Teachers(teacher_id, teacher_name). Write a SQL query that shows course_name and teacher_name only for courses taught by 'Ms. Rivera'.
The query is: SELECT Courses.course_name, Teachers.teacher_name FROM Courses JOIN Teachers ON Courses.teacher_id = Teachers.teacher_id WHERE Teachers.teacher_name = 'Ms. Rivera'; This joins courses to teachers and filters the result to courses taught by Ms. Rivera. - 12
Explain the difference between SELECT, WHERE, and JOIN in SQL. Use one sentence for each keyword.
Think about columns, rows, and tables.
SELECT chooses which columns appear in the result. WHERE filters rows based on one or more conditions. JOIN combines rows from two tables using a related column.