Do I need to learn SQL for a database exam?
You may expect a slightly different approach when databases are just an addition to your degree curriculum in economics, compared to when you’re studying computer science.
In each case, however, you will most probably have to learn SQL.
In this article, we’ll try to get you prepared for your first database exam.
,
Question 1: Simple Select Statement
Task: You are given the following table named student. Write a query which will SELECTthe first and last name of each student.
Answer: Explanation: This is one of the simplest SQL statements we can write.
It consists of two clauses: SELECT and FROM.
SELECT lists all the columns we want to show while FROMspecifies the table from which we want to que.
,
Question 2: Simple Select with Where
Task: You are given the following table named class.
Show the name and the rating of every class with the rating above 4.0.
Answer: Explanation: In this case, we need to add a WHERE clause which is used to filter rows.
Since we need courses with a rating above 4.0, we use the following condition: rating > 4.0.
By the same token, you can use other o.
,
Question 3: Select with A Complex Where Clause
Task: You are given the following table named lecturer.
Show the first name, last name, and the rating of each lecturer from departments with Department ID of 1 or 2, whose rating is 4.50 or higher.
Answer: Explanation: In this question, we introduce logical operators (AND and OR) in the WHERE clause.
They are used to specify more than one conditio.
,
Question 4: Select with Row Sorting
Task: You are given the following table named lecturer.
Show all the information for each lecturer.
Sort the rows in the ascending order by department_id, and then in the descending order by the rating.
Answer: Explanation: This time, we have to select all the columns.
Rather than write all the column names separated by commas, we use the asterisk .
,
Question 5: Select with A Join
Task: You are given the following two tables: lecturer and department.
Write a query that will show the first and last names of each lecturer alongside the name of his/her department.
The name of the department should be shown in a column named department_name.
Answer: Explanation: In this exercise, we have to join two tables together.
That’s why t.
,
Question 6: Simple Aggregate Functions
Task: You are given the following table named class.
Write a query that will show the average class rating in a column named avg_rating.
Answer: Explanation: Even though the question may seem difficult at first, it’s actually pretty simple to show the average value from a column in SQL.
All we have to do is use the AVG(column_name) aggregate functi.
,
Question 7: Aggregate Functions with Group by
Task: You are given the following two tables: lecturer and department.
Write a query that will show the ID and the name of each department alongside the average rating over all lecturers from that department.
Sort the rows in the descending order by the average rating.
Answer: Explanation: This is a slightly more complex query to write.
We need to .
,
Question 8: Simple Subqueries
Task: You are given the following table named lecturer.
Write a query that will show all the information for every lecturer whose rating is greater than or equal to the rating of the lecturer with ID.
4) Answer: Explanation: Subqueries are “queries within queries” and are introduced within parentheses.
They may appear in various places of an SQL ins.
,
Question 9: Creating Tables
Task: Write a CREATE instruction in SQL that will create a table named degree_coursewith the following fields: 1. id: an integer field; cannot be null 2. max_students: an integer field; cannot be null 3. name: a text field of up to 64 characters; cannot be null 4. is_paid: a yes/no field; may be null Answer: Explanation: Here, we no longer write a .
,
What are entry-level database classes?
Entry-level database classes typically focus on retrieving data with SELECT statements written in the SQL language.
In these classes, your instructor assumes that there is a database with a predefined set of tables already available, and that all you have to do is to get the right information out of those tables.
,
What is the simplest SQL statement?
Explanation:
- This is one of the simplest SQL statements we can write
It consists of two clauses:
SELECT and FROM.
SELECT lists all the columns we want to show while FROM specifies the table from which we want to query.
Additional materials:Take a look at “Part 2:Selecting from one table” in our SQL Basics course.
,
Where can I get a basic database class?
It’s no wonder that many universities offer basic classes in databases to a wide range of students.
If you like learning SQL using hands-on exercises, then you’ve got to try LearnSQL.com.
These database classes may be taught in a variety of ways depending on the type of degree curriculum.
