How SQL communicates with the database engine - Try It Yourself

How SQL Communicates with the Database Engine

📖 Scenario: You are working as a junior database assistant in a small company. Your task is to understand how SQL commands are sent to the database engine and how the engine processes these commands to give results.This project will guide you through creating a simple table, inserting data, and querying the data step-by-step to see how SQL communicates with the database engine.

🎯 Goal: Build a simple SQL script that creates a table, inserts data, and queries the data to demonstrate how SQL commands communicate with the database engine.

📋 What You'll Learn

Create a table named employees with columns id, name, and department

Insert three specific employee records into the employees table

Write a SQL query to select all employees from the employees table

Add a WHERE clause to filter employees by department

💡 Why This Matters

🌍 Real World

Understanding how SQL commands are structured and sent to the database engine helps you manage and query real databases effectively.

💼 Career

Database administrators and developers use these basic SQL commands daily to create, modify, and retrieve data from databases.

1

Create the employees table

Write a SQL statement to create a table called employees with three columns: id as an integer primary key, name as text, and department as text.

SQL

-- Your code here to create the employees table

Hint

Use CREATE TABLE followed by the table name and define each column with its data type.

2

Insert employee records

Write three SQL INSERT INTO statements to add these employees to the employees table: (1, 'Alice', 'HR'), (2, 'Bob', 'IT'), and (3, 'Charlie', 'Finance').

SQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT
);
-- Your code here to insert employee records

Hint

Use INSERT INTO employees (id, name, department) VALUES (...) for each employee.

3

Query all employees

Write a SQL SELECT statement to get all columns and all rows from the employees table.

SQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT
);

INSERT INTO employees (id, name, department) VALUES (1, 'Alice', 'HR');
INSERT INTO employees (id, name, department) VALUES (2, 'Bob', 'IT');
INSERT INTO employees (id, name, department) VALUES (3, 'Charlie', 'Finance');

-- Your code here to select all employees

Hint

Use SELECT * FROM employees; to get all data.

4

Filter employees by department

Write a SQL SELECT statement to get all columns for employees where the department is 'IT'. Use a WHERE clause.

SQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT
);

INSERT INTO employees (id, name, department) VALUES (1, 'Alice', 'HR');
INSERT INTO employees (id, name, department) VALUES (2, 'Bob', 'IT');
INSERT INTO employees (id, name, department) VALUES (3, 'Charlie', 'Finance');

SELECT * FROM employees;

-- Your code here to select employees from IT department

Hint

Use WHERE department = 'IT' to filter the results.

Practice

1. What is the main role of SQL when working with a database engine?

easy

A. To store data physically on the disk

B. To send instructions that the database engine executes

C. To design the hardware of the database server

D. To create user interfaces for database applications

Solution

1. Step 1: Understand SQL's purpose

   SQL is a language used to communicate commands to the database engine.

2. Step 2: Identify the database engine's role

   The database engine executes these commands to manage data.

3. Final Answer:

   To send instructions that the database engine executes -> Option B

4. Quick Check:

   SQL sends instructions = A [OK]

Hint: SQL sends commands to the engine, not hardware or UI [OK]

Common Mistakes:

• Confusing SQL with physical data storage
• Thinking SQL designs hardware
• Assuming SQL creates user interfaces

2. Which of the following is the correct way to write a SQL command to select all columns from a table named users?

easy

A. SELECT * FROM users;

B. GET ALL FROM users;

C. SHOW * FROM users;

D. FETCH ALL users;

Solution

1. Step 1: Recall SQL SELECT syntax

   The correct syntax to get all columns is SELECT * FROM table_name;.

2. Step 2: Match syntax to options

   Only SELECT * FROM users; matches the correct SQL syntax.

3. Final Answer:

   SELECT * FROM users; -> Option A

4. Quick Check:

   Correct SELECT syntax = B [OK]

Hint: Use SELECT * FROM table; to get all columns [OK]

Common Mistakes:

• Using non-SQL keywords like GET or FETCH
• Omitting FROM keyword
• Using SHOW incorrectly

3. Given the table employees with columns id, name, and salary, what will this SQL query return?

SELECT name FROM employees WHERE salary > 50000;

medium

A. Syntax error due to incorrect WHERE clause

B. All columns of employees earning more than 50000

C. Names of employees earning less than 50000

D. Names of employees earning more than 50000

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only the name column.

2. Step 2: Analyze the WHERE condition

   It filters rows where salary > 50000, so only employees earning more than 50000 are included.

3. Final Answer:

   Names of employees earning more than 50000 -> Option D

4. Quick Check:

   SELECT name with salary > 50000 = D [OK]

Hint: WHERE filters rows; SELECT chooses columns [OK]

Common Mistakes:

• Confusing column selection with all columns
• Misreading > as <
• Thinking WHERE causes syntax error

4. Identify the error in this SQL query:

SELEC name FROM customers;

medium

A. Table name is incorrect

B. Missing FROM keyword

C. Misspelled SELECT keyword

D. No error, query is correct

Solution

1. Step 1: Check SQL keywords

   The keyword SELECT is misspelled as SELEC.

2. Step 2: Verify other parts

   The FROM keyword and table name customers are correct.

3. Final Answer:

   Misspelled SELECT keyword -> Option C

4. Quick Check:

   Keyword spelling error = C [OK]

Hint: Check spelling of SQL keywords carefully [OK]

Common Mistakes:

• Ignoring small typos in keywords
• Assuming table name is wrong without checking
• Thinking missing FROM when it is present

5. You want to get the total number of rows in the orders table. Which SQL command correctly communicates this request to the database engine?

hard

A. SELECT COUNT(*) FROM orders;

B. COUNT * FROM orders;

C. GET TOTAL orders;

D. SELECT TOTAL FROM orders;

Solution

1. Step 1: Recall aggregate function syntax

   To count rows, use SELECT COUNT(*) FROM table;.

2. Step 2: Match options to correct syntax

   Only SELECT COUNT(*) FROM orders; uses the correct SQL syntax with SELECT and COUNT(*).

3. Final Answer:

   SELECT COUNT(*) FROM orders; -> Option A

4. Quick Check:

   Correct COUNT syntax = A [OK]

Hint: Use SELECT COUNT(*) FROM table; to count rows [OK]

Common Mistakes:

• Omitting SELECT keyword
• Using non-SQL commands like GET or TOTAL
• Misplacing COUNT function