CS: Databases and SQL Basics Worksheet

Question 1

In a relational database, data is often stored in tables. Explain what a table, row, and column represent.

Accepted Answer

A table stores data about one type of thing, such as students or products. A row represents one record in the table. A column represents one field or attribute, such as a name, ID number, or price.

Question 2

A Students table has these columns: student_id, first_name, last_name, grade_level. Write a SQL query that selects all columns from the Students table.

Accepted Answer

The query is SELECT * FROM Students;. The asterisk means all columns should be returned from the Students table.

Question 3

A Books table has these columns: book_id, title, author, year_published. Write a SQL query that shows only the title and author of every book.

Accepted Answer

The query is SELECT title, author FROM Books;. This returns only the title and author columns for every row in the Books table.

Question 4

A Products table has these columns: product_id, name, category, price. Write a SQL query that selects all products with a price greater than 25.

Accepted Answer

The query is SELECT * FROM Products WHERE price > 25;. The WHERE clause filters the rows so only products with a price greater than 25 are returned.

Question 5

A Movies table has these columns: movie_id, title, rating, release_year. Write a SQL query that selects movie titles released in 2020 or later, sorted from newest to oldest.

Accepted Answer

The query is SELECT title FROM Movies WHERE release_year >= 2020 ORDER BY release_year DESC;. The WHERE clause filters the movies, and ORDER BY release_year DESC sorts the newest movies first.

Question 6

A Customers table has these columns: customer_id, first_name, last_name, email. Identify the best primary key and explain why it is a good choice.

Accepted Answer

The best primary key is customer_id because it should uniquely identify each customer. Names and emails might change or might not be unique, but an ID can be assigned to stay unique for every row.

Question 7

A Classes table has these columns: class_id, class_name, teacher_name. Write a SQL INSERT statement that adds a class with class_id 12, class_name 'Biology', and teacher_name 'Ms. Patel'.

Accepted Answer

The statement is INSERT INTO Classes (class_id, class_name, teacher_name) VALUES (12, 'Biology', 'Ms. Patel');. This adds one new row to the Classes table.

Question 8

An Employees table has these columns: employee_id, name, department, hourly_rate. Write a SQL UPDATE statement that changes the hourly_rate to 18.50 for the employee with employee_id 7.

Accepted Answer

The statement is UPDATE Employees SET hourly_rate = 18.50 WHERE employee_id = 7;. The WHERE clause is important because it limits the update to only one employee.

Question 9

A table named Pets has these rows: (1, 'Milo', 'cat'), (2, 'Bella', 'dog'), (3, 'Luna', 'cat'). The columns are pet_id, pet_name, species. What rows are returned by SELECT pet_name FROM Pets WHERE species = 'cat';?

Accepted Answer

The query returns Milo and Luna. These are the pet_name values from the rows where the species value is 'cat'.

Question 10

Two tables are shown. Students has student_id and student_name. Enrollments has enrollment_id, student_id, course_name. Explain how student_id can be used to connect these tables.

Accepted Answer

The student_id column in Students is the primary key for each student. The student_id column in Enrollments can be a foreign key that points to a student, allowing each enrollment record to be matched with the correct student.

Question 11

Using Students(student_id, student_name) and Enrollments(enrollment_id, student_id, course_name), write a SQL query that lists each student_name with the course_name they are enrolled in.

Accepted Answer

The query is SELECT Students.student_name, Enrollments.course_name FROM Students JOIN Enrollments ON Students.student_id = Enrollments.student_id;. The JOIN connects rows where the student_id values match.

Question 12

A database designer stores a customer's full address in one column named full_address. Explain one reason it might be better to separate the address into columns such as street, city, state, and zip_code.

Accepted Answer

Separating the address into smaller columns makes the data easier to search, sort, and update. For example, the database can quickly find all customers in one city or sort customers by zip_code.

CS: Databases and SQL Basics

Related Cheat Sheets

More CS Worksheets

More Grade 9-12 Worksheets