9.4 DATABASES (CIE)

TOPIC 9.4 | DATABASES

ON THIS PAGE
Read, understand and complete structured query language (SQL) scripts to query data stored in a single database table.
Limited to:
– SELECT
– FROM
– WHERE
– ORDER BY
– SUM
– COUNT
• Identifying the output given by an SQL statement that will query the given contents of a database table

ALSO IN THIS SECTION
9.1 DEFINING A DATABASE
9.2 DATA TYPES
9.3 PRIMARY KEYS
9.4 SQL (STRUCTURED QUERY LANGUAGE)
KEY TERMINOLOGY

WHAT IS SQL

SQL, short for Structured Query Language. SQL is the language we use to communicate with databases, SQL can be used within many other languages such as Python or Go to create a link and method of accessing, searching and manipulating the data within the database. SQL is the most popular method of querying / requesting and changing data within a database, if you are handling data then you should be familiar with SQL.

In this section we look at some of the most popular SQL methods.

Key SQL Clauses and Functions

SELECT: Used to specify the columns to be displayed in the query's result.
FROM: Indicates the table from which to retrieve the data.
WHERE: Applies a condition to filter the records.
ORDER BY: Sorts the result set in either ascending or descending order.
SUM: Aggregates data by calculating the sum of a numeric column.
COUNT: Counts the number of rows that match a specified criterion.

SECTION 1 | SELECT

Using SQL to select data from the database, pulls data from the original database and creates a new report with just the data you have selected. It does not change the original data, changing data will come later in the delete, insert and update methods.

SELECT ALL
SELECT WHERE
SELECT BETWEEN
SELECT AND / OR
SELECT WILDCARD

Select specified fields of data. Only return the columns that you want from a specified table.

SELECT column1, column2 FROM table

Example:
SELECT gameID, gameName FROM currently_playing

Select data that meets a specified condition. For example select all players with a score greater than 10.

SELECT fields FROM table WHERE field (operator) condition

Example:
SELECT * FROM currently_playing WHERE gameID = 3

To select data between certain values.

SELECT fields FROM table WHERE field BETWEEN value AND value

Example:
SELECT * FROM currently_playing WHERE gameID BETWEEN 1 AND 3

Two make a selection that requires more than one conditional parameter to be met.

SELECT fields FROM table WHERE condition AND condition

Example:
SELECT * FROM currently_playing WHERE gameName = "Pac Land" AND gameID = 3

You can create a search to find a field that contains a value within a string of values by using the wildcard. The wildcard use the % sign or * sign with some DB such as MS Access.

SELECT fields FROM table WHERE field LIKE %condition%

Example:
SELECT * FROM currently_playing WHERE gameName LIKE "%Land%"

In this example games such as PacLand should be found.

SECTION 2 | ORDER BY

The ORDER BY clause in SQL is a powerful tool used to sort the results of a query in either ascending or descending order, based on one or more columns. Understanding how to use ORDER BY effectively is crucial for organizing data in a meaningful way.

ORDER BY

This will determine how the results are sorted. To sort the results by a single column, you simply specify that column in the ORDER BY clause.

SELECT column1, column2 FROM table_name ORDER BY column1 DESC, column2 DESC;

Example:
SELECT name, age FROM users ORDER BY age DESC;

In the example above the results will be selected in descending order.

ASC: Specifies an ascending order (default).
DESC: Specifies a descending order.

Multiple Column Sorting
You can also sort by more than one column. The sorting is performed on the first column, and then within each group of the first column, sorting is done based on the second column, and so on.
Example:
SELECT name, age, city FROM users ORDER BY city ASC, age DESC;

This query first sorts users by city in ascending order. Within each city, users are sorted by age in descending order.

SECTION 3 | SUM

The SUM function in SQL is an aggregate function that is used to calculate the total sum of a numeric column in a database table. It's a powerful tool for data analysis, especially when you need to add up values from a large dataset

Basic use of the SUM method

SELECT SUM (column_name) FROM table_name WHERE condition;

Example
SELECT SUM (salary) FROM employees WHERE department = 'Sales';

This query calculates the total sum of salaries for all employees in the Sales department.

SECTION 4 | COUNT

The COUNT function in SQL is an essential aggregate function used to count the number of rows in a database table that match a specified condition. It's widely used in data analysis and reporting, as it helps in understanding the volume or frequency of data in a database.

COUNT
ADVANCED COUNT

Basic use of the SUM method

SELECT COUNT (column_name or expression or *) FROM table_name WHERE condition;

Example
SELECT COUNT(*) FROM orders WHERE status = 'Shipped';

This query counts the number of orders with the status 'Shipped'.

COUNT with DISTINCT

COUNT(DISTINCT column_name) counts the number of unique non-NULL values in a column.

Example:
SELECT COUNT(DISTINCT customer_id) FROM orders;
This counts the number of unique customers who have placed orders.

Combining with GROUP BY
SELECT department, COUNT(*) FROM employees GROUP BY department;
This query counts the number of employees in each department.

Practical Considerations

Performance | On large tables, counting rows can be resource-intensive, especially with COUNT(*). Proper indexing can improve performance.
Use in Subqueries | COUNT can be used in subqueries for more complex queries, such as finding the number of items in a category that exceed a certain threshold.
Data Type of Result | The result of a COUNT function is an integer. It's important to consider this when using the result in further calculations or comparisons.

The COUNT function is a fundamental tool in SQL for data analysis. It provides a simple yet powerful way to quantify the number of records in a database that meet certain criteria. Whether used alone or in combination with other SQL functions and clauses, COUNT offers valuable insights into the data, aiding in effective decision-making and reporting.

EmployeeID	Name	Department	Salary	JoinDate
1	JohnDoe	Engineering	70000	2020-01-10
2	Jane Doe	Marketing	50000	2019-06-23
3	Jim Beam	Engineering	80000	2021-03-15
4	Jack Ray	Sales	45000	2022-07-01
5	Jill May	Marketing	55000	2021-11-09

1: What does the following SQL query return?
SELECT COUNT(*) FROM Employees;
a) The total number of employees in each department.
b) The total number of employees in the Employees table.
c) The sum of salaries of all employees.
d) The average salary of employees.

2: What will be the output of the following query?
SELECT Department, SUM(Salary) FROM Employees GROUP BY Department;

3: Which employees' names will be listed in the result?
SELECT Name FROM Employees WHERE Salary > 60000 ORDER BY JoinDate DESC;

4: What will the following query return?
SELECT MAX(Salary) FROM Employees WHERE Department = 'Engineering';
a) The highest salary in the entire Employees table.
b) The names of employees in the Engineering department.
c) The highest salary in the Engineering department.
d) The total number of employees in the Engineering department.

5: Who will be listed in the output and what will their salary be?
SELECT Name, Salary FROM Employees ORDER BY Salary ASC LIMIT 1;

6: What does this SQL statement produce?
SELECT COUNT(DISTINCT Department) FROM Employees;
a) The total number of departments.
b) The total number of employees.
c) The average salary across all departments.
d) The total salary paid in each department.

7: Which employees' records will be included in the output?
SELECT * FROM Employees WHERE JoinDate BETWEEN '2020-01-01' AND '2021-12-31';

8: How will the departments and their average salaries be displayed?
SELECT Department, AVG(Salary) FROM Employees GROUP BY Department;

9: Which employee names will appear in the result?
SELECT Name FROM Employees WHERE Department = 'Sales' AND Salary > 40000;

10: What does the following query return?
SELECT Name, JoinDate FROM Employees WHERE Name LIKE 'J%';
a) The names and join dates of all employees.
b) The names and join dates of employees whose names start with 'J'.
c) The names and departments of employees whose names start with 'J'.
d) The total number of employees whose names start with 'J'.