CS: Databases and SQL Basics
Understanding tables, queries, keys, and simple SQL commands
CS: Databases and SQL Basics
Understanding tables, queries, keys, and simple SQL commands
CS - Grade 9-12
- 1
In a relational database, data is often stored in tables. Explain what a table, row, and column represent.
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. - 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.
Use SELECT with the symbol that means all columns.
The query is SELECT * FROM Students;. The asterisk means all columns should be returned from the Students table. - 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.
The query is SELECT title, author FROM Books;. This returns only the title and author columns for every row in the Books table. - 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.
Use a WHERE clause with the greater than operator.
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. - 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.
DESC means descending order.
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. - 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.
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. - 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'.
List the column names first, then list the matching values in the same order.
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. - 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.
Do not forget the WHERE clause when updating a specific row.
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. - 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';?
The query returns Milo and Luna. These are the pet_name values from the rows where the species value is 'cat'. - 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.
A foreign key refers to a primary key in another table.
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. - 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.
Use JOIN and ON to match the shared student_id column.
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. - 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.
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.