Databases

SQL

Introduction

• Structured Query Language (SQL) is the standard query language for writing scripts to obtain useful information from a database.

• SQL performs actions such as defining tables, changing tables, add data to tables, search data from the table, perform calculations.

SQL scripts

An SQL script is a list of SQL commands that perform a given task, often stored in a file so the script can be reused.

SELECT .... FROM

• SELECT: Fetches specified fields (columns) from a table; queries

  always begin with SELECT.

• FROM: Identifies the table to use.

• SELECT * – this specifies that all fields (columns) are to be shown.

SELECT ColumnName
FROM TableName

SQL Examples

• Display the first name of all students

• Display the first name and last name of all students

• Display all columns in the table

SELECT .. FROM ... WHERE

• This retreives only specific data.

• logical operators for single condition.

  • = (Equal to)

  • < (Less than)

  • > (Greater than)

  • <= (Less or equal to)

  • >= (Greater or equal to)

  • <> (Not equal to)

Multiple Conditions

• Operators used for multiple conditions

  • AND. (Returns data that meets both conditions)

  • OR. (Returns data that meets one condition or both)

Multiple Choice

What does the SELECT statement in SQL do?

• Creates a new database
• Retrieves data from a database
• Updates data in a database
• Deletes data from a database

Multiple Choice

Which SQL keyword is used to select all columns from a table?

• SELECT *
• SELECT ALL FIELDS
• SELECT ALL COLUMNS
• SELECT ALL

Multiple Choice

How would you retrieve the "FirstName" and "LastName" columns from the "Employees" table?

• SELECT LastName, FirstName FROM Employees;
• SELECT * FROM Employees;
• SELECT FirstName, LastName FROM Employees;
• SELECT FirstName, LastName FROM Staff;

Multiple Choice

Which clause is used to filter the results of a SELECT statement?

• GROUP BY
• SELECT
• FILTER
• WHERE

Multiple Choice

What does the following SQL query do?

SELECT ProductName, Price FROM Products WHERE Price > 100;

• It retrieves the names and prices of products with a price higher than 100.
• It retrieves all products regardless of price.
• It deletes products with a price below 100.
• It updates the prices of products above 100.

Multiple Choice

Which SQL statement correctly retrieves all columns from the "Students" table where the "Grade" is greater than 85?

• SELECT * FROM Students WHERE Grade > 85;
• SELECT * FROM Students WHERE Grade > 85.0;
• SELECT * FROM Students WHERE Grade > '85';
• SELECT * FROM Students WHERE Grade >= 85;

Multiple Choice

How would you write a query to retrieve the "FirstName" and "LastName" of employees from the "Employees" table who are in the "Sales" department?

• SELECT * FROM Employees WHERE Department = 'Sales';
• SELECT FirstName, LastName FROM Employees WHERE Department = 'Marketing';
• SELECT FirstName, LastName FROM Employees WHERE Department = 'HR';
• SELECT FirstName, LastName FROM Employees WHERE Department = 'Sales';

Multiple Choice

How would you retrieve all products from the "Products" table that have a price less than $50?

• SELECT * FROM Products WHERE price > 50;
• SELECT * FROM Products WHERE price = 50;
• SELECT * FROM Products WHERE price <= 50;
• SELECT * FROM Products WHERE price < 50;

Multiple Choice

Which of the following queries retrieves employees from the "Employees" table who are not in the "IT" department?

• SELECT * FROM Employees WHERE department = 'HR'
• SELECT * FROM Employees WHERE department != 'IT'
• SELECT * FROM Employees WHERE department = 'IT'
• SELECT * FROM Employees WHERE department <> 'IT'

Multiple Choice

How would you write a query to select the "CustomerName" and "City" from the "Customers" table for customers who live in "New York"?

• SELECT CustomerName, City FROM Customers WHERE City = 'Miami';
• SELECT CustomerName, City FROM Customers WHERE City = 'New York';
• SELECT CustomerName, City FROM Customers WHERE City = 'Chicago';
• SELECT CustomerName, City FROM Customers WHERE City = 'Los Angeles';

Multiple Choice

Which SQL statement is used to retrieve all columns from the "Transactions" table where the "TransactionType" is 'Credit'?

• SELECT * FROM Transactions WHERE TransactionType = 'Credit';
• SELECT * FROM Transactions WHERE TransactionType = 'Transfer';
• SELECT * FROM Transactions WHERE TransactionType = 'Cash';
• SELECT * FROM Transactions WHERE TransactionType = 'Debit';

Multiple Choice

What will the following SQL query return?

SELECT FirstName, LastName FROM Employees WHERE Salary > 50000;

• A list of full names of employees with a salary greater than 50000
• A list of employee IDs with a salary greater than 50000
• A list of last names of employees with a salary greater than 50000
• A list of first names and last names of employees with a salary greater than 50000.

Multiple Choice

Which data type would be most appropriate for storing a student’s date of birth in a school database?

• Date

• Text

Multiple Choice

A database stores the percentage of students who passed an exam. Which data type should be used for this percentage?

• Integer

• Float/Real

• Text

• Boolean

Multiple Choice

The Is_Enrolled field in a student database, which indicates whether a student is currently enrolled, should use which data type?

• Date
• String
• Boolean
• Integer

Multiple Choice

Which data type would be best for storing a student’s grade level (e.g., 7, 8, 9) in a school database?

• float
• boolean
• string
• integer

Multiple Choice

In a school database, the Email field should be stored as which data type?

• integer
• boolean
• date
• string

Multiple Choice

The GPA (Grade Point Average) field in a student database, which may have decimal places, should be stored as which data type?

• integer
• string
• boolean
• float or double

Multiple Choice

If you were to store a list of extracurricular activities a student is involved in, which data type would be most suitable?

• integer
• text

• float
• boolean

Multiple Choice

Which data type would you use to store a student's contact number in a school database?

• boolean
• string
• array
• integer

Match

Match the following

• Account_Number

• Account_Holder_Name

• Account_Balance

• Date_Of_Opening

• Is_Active

INTEGER

String/Text

Real

Date

Boolean

• INTEGER

• String/Text

• Real

• Date

• Boolean