SQL Data Manipulation (Week 12 Test) Flashcards
1
Q
What is SQL?
A
SQL or Structured Query Language is a language that has emerged from the development of the relational model
SQL has become the standard relational database language
2
Q
What are the primary objectives of SQL?
A
- Create the database and relation structures
- Perform basic data management tasks
- Perform simple and complex queries
- Minimize user effort and ensure ease of learning
- Be portable across different DBMS
3
Q
What are the two major components of SQL?
A
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
4
Q
What does Data Definition Language (DDL) allow?
A
Allows definition of database structure, specification of data types and constraints, and controlling access to the data
Examples include CREATE table, ALTER table, DROP table
5
Q
What is the purpose of Data Manipulation Language (DML)?
A
Allows retrieving (querying) and updating of data
Examples include SELECT, INSERT, UPDATE, DELETE
6
Q
How is SQL described in terms of procedural structure?
A
It is non-procedural; you specify what information you require, rather than how to get it
7
Q
What is the syntax for creating a table in SQL?
A
CREATE TABLE DatabaseName.NewTableName (ColumnName1 DataType OptionalConstraint, …)
Optional table constraints can also be included
8
Q
What are the main data types available in SQL?
A
- Exact numeric: INT, SMALLINT, BIGINT, DECIMAL
- Approximate numeric: DOUBLE, FLOAT, REAL
- Date/Time: DATETIME, DATE, TIME, TIMESTAMP
- Character/String: CHAR, VARCHAR
9
Q
What is an integrity constraint in SQL?
A
Rules that ensure the accuracy and consistency of data within a database
10
Q
What does the NOT NULL constraint enforce?
A
Specifies that a column must contain a value
This is used for columns that cannot have null values
11
Q
What is the purpose of the PRIMARY KEY constraint?
A
Ensures that each value in the column is unique and not null
12
Q
What does the FOREIGN KEY constraint do?
A
Links a column to the primary key of another table, ensuring referential integrity
13
Q
What is the syntax for altering a table to add a new column?
A
ALTER TABLE TableName ADD ColumnName DataType
14
Q
What SQL command is used to remove a table?
A
DROP TABLE TableName
15
Q
What is the general syntax of a SELECT statement?
A
SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [,…]}
FROM TableName [alias] [, …]
[WHERE condition]
[GROUP BY columnList]
[HAVING condition]
[ORDER BY columnList]
16
Q
What is the purpose of the WHERE clause in a SELECT statement?
A
Filters rows subject to some condition
17
Q
What does the ORDER BY clause do in a SQL query?
A
Specifies the order of the output
18
Q
What is the purpose of the DISTINCT keyword in SQL?
A
Eliminates duplicate rows from the result set
19
Q
What is a calculated field in SQL?
A
A field that is derived from other fields in the data set, often using arithmetic operations
20
Q
Fill in the blank: The command to modify an existing column in a table is ______.
A
ALTER TABLE
21
Q
True or False: SQL commands are case sensitive.
A
False
Most components of an SQL statement are case insensitive except for literal character data
22
Q
What SQL command is used to eliminate duplicates in a query result?
A
DISTINCT
DISTINCT is applied in the SELECT statement to return only unique values.
23
Q
How do you produce a list of monthly salaries for all staff?
A
SELECT StaffNo, Forename, Surname, Salary/12
FROM STAFF;
This query divides the annual salary by 12 to calculate the monthly salary.
24
Q
What clause is used to rename a column in SQL?
A
AS clause
The AS clause allows you to give a column a temporary name in the result set.
25
What SQL command lists all staff with a salary greater than 25,000?
SELECT StaffNo, Forename, Surname, 'Position', Salary
FROM STAFF
WHERE Salary > 25000;
## Footnote
This query filters results using the WHERE clause to find staff earning above a specified amount.
26
Which SQL clause is used to find records with a salary not equal to 40,000?
WHERE Salary <> 40000
## Footnote
The <> operator is used to specify 'not equal to' in SQL.
27
How do you list addresses of all branch offices in London or Glasgow?
SELECT * FROM BRANCH
WHERE City = 'London' OR City = 'Glasgow';
## Footnote
This query retrieves all columns from the BRANCH table where the city matches either London or Glasgow.
28
What SQL command lists all staff with a salary between 20,000 and 30,000?
SELECT StaffNo, Forename, Surname, 'Position', Salary
FROM STAFF
WHERE Salary >= 20000 AND Salary <= 30000;
## Footnote
This query uses compound conditions in the WHERE clause to filter results within a specific range.
29
What SQL command lists all managers and assistants?
SELECT StaffNo, Forename, Surname, 'Position' FROM STAFF
WHERE `Position` IN ('Manager', 'Assistant');
## Footnote
The IN operator checks if the Position is either Manager or Assistant.
30
How can the IN operator be expressed without using it?
WHERE `Position` = 'Manager' OR `Position` = 'Assistant';
## Footnote
This alternative method uses multiple OR conditions instead of the IN operator.
31
What SQL command finds all owners with 'Glasgow' in their address?
SELECT OwnerNo, Forename, Surname, Address, TelNo
FROM OWNER
WHERE Address LIKE '%Glasgow%';
## Footnote
The LIKE operator is used for pattern matching in SQL.
32
What do the SQL special pattern matching symbols '%' and '_' represent?
% represents a sequence of zero, one or more characters; _ represents a single character.
## Footnote
These symbols are used in conjunction with the LIKE operator for flexible pattern matching.
33
What SQL command lists details of all viewings on property PG4 without a supplied comment?
SELECT RenterNo, `Date`
FROM VIEWING
WHERE PropertyNo = 'PG4' AND Comment IS NULL;
## Footnote
This query uses IS NULL to check for records where a comment is not provided.
34
What is the negated version of IS NULL in SQL?
IS NOT NULL
## Footnote
This operator checks for records that do have a value in the specified column.
35
What is the purpose of SQL?
To manage and manipulate relational databases.
## Footnote
SQL stands for Structured Query Language and is essential for database operations.
36
What are the major components of SQL?
* Data Query Language (DQL)
* Data Definition Language (DDL)
* Data Manipulation Language (DML)
* Data Control Language (DCL)
## Footnote
Each component serves a specific purpose in database management.
37
What is the function of the SELECT statement in SQL?
To retrieve data from a database.
## Footnote
SELECT is one of the most commonly used commands in SQL to fetch records.
38
What SQL command is used to sort query results?
ORDER BY
## Footnote
ORDER BY sorts the result set based on one or more columns.
39
What SQL command is used to retrieve data from a database?
SELECT
## Footnote
SELECT is the primary command for querying data from a database.
40
What are the five aggregate functions defined by the SQL ISO standard?
* COUNT
* SUM
* AVG
* MIN
* MAX
## Footnote
These functions perform calculations on data and return a single value.
41
What is the purpose of the GROUP BY clause in SQL?
To group rows that have the same values in specified columns into summary rows
## Footnote
GROUP BY is often used with aggregate functions to produce summary statistics.
42
How do you specify a descending order in an ORDER BY clause?
ORDER BY column_name DESC
## Footnote
DESC keyword indicates that the results should be sorted in descending order.
43
What does the HAVING clause do in SQL?
Filters groups based on aggregate function results
## Footnote
HAVING is used after GROUP BY to apply conditions to the grouped results.
44
What is a sub-query?
A query nested inside another SQL query
## Footnote
Sub-queries can be used in SELECT, INSERT, UPDATE, and DELETE statements.
45
What SQL command is used to update data in a database?
UPDATE
## Footnote
UPDATE modifies existing records in a table.
46
What is the difference between WHERE and HAVING clauses?
WHERE filters rows before grouping; HAVING filters groups after aggregation
## Footnote
This distinction is crucial for correct SQL query results.
47
Fill in the blank: The SQL command used to delete records from a database is _______.
DELETE
## Footnote
DELETE is used to remove existing records from a table.
48
True or False: The ORDER BY clause can only sort by a single column.
False
## Footnote
ORDER BY can sort by multiple columns by separating them with commas.
49
What is the purpose of the SUM function in SQL?
Returns the sum of values in a specified column
## Footnote
SUM is often used in conjunction with GROUP BY.
50
What does the COUNT(*) function do?
Counts all rows in a table, including nulls
## Footnote
COUNT(*) returns the total number of rows, regardless of nulls or duplicates.
51
How do you perform a JOIN in SQL?
Include more than one table in the FROM clause and specify the JOIN condition
## Footnote
JOINs allow combining rows from two or more tables based on related columns.
52
Fill in the blank: The SQL command to create tables and set constraints is part of _______.
SQL DDL
## Footnote
DDL stands for Data Definition Language.
53
What is the purpose of the IN keyword in SQL?
Specifies multiple possible values for a column in a WHERE clause
## Footnote
IN is used to filter records that match any value in a list.
54
What does the EXISTS keyword do in SQL?
Checks for the existence of rows in a subquery
## Footnote
EXISTS returns true if the subquery returns one or more rows.
55
What is the function of the ANY keyword in SQL?
Returns true if any value in a subquery matches the condition
## Footnote
ANY is often used with comparison operators.
56
What is the purpose of the AVG function in SQL?
Returns the average of values in a specified column
## Footnote
AVG is used to calculate the mean of a numeric column.
57
How do you use the JOIN clause to include an alias for a table?
Use the table name followed by the alias separated by a space
## Footnote
Aliases simplify referencing tables in complex queries.
58
Fill in the blank: The SQL command used to combine the result sets of two or more SELECT statements is called _______.
UNION
## Footnote
UNION combines results from multiple SELECT statements into a single result set.
59
What does the MIN function do in SQL?
Returns the smallest value in a specified column
## Footnote
MIN can be used with numeric and date fields.
60
What SQL statement is used to add new rows of data to a table?
INSERT
61
What SQL statement is used to modify existing data in a table?
UPDATE
62
What SQL statement is used to remove rows of data from a table?
DELETE
63
What does the ANY operator do in SQL?
The condition is true if it is satisfied by any one or more values produced by the subquery.
64
What is the difference between ANY and SOME in SQL?
There is no difference; SOME can be used instead of ANY.
65
What does the ALL operator do in SQL?
The condition is only true if it is satisfied by all values produced by the subquery.
66
What is a non-correlated subquery?
A subquery that does not depend on data from the outer query and executes once for the entire outer query.
67
What is a correlated subquery?
A subquery that makes use of data from the outer query and executes once for each row of the outer query.
68
What do EXISTS and NOT EXISTS check in SQL?
They check for the existence or non-existence of rows in the subquery result table.
69
What is the result of EXISTS if the subquery returns a non-empty set?
TRUE
70
What is the result of NOT EXISTS if the subquery returns an empty result table?
TRUE
71
What is union compatibility in SQL?
Two SELECT queries can be combined only if they have the same number of columns and the datatype of each column matches.
72
What does the UNION operation do?
It produces a table containing all rows in either of the two tables or both.
73
What does the INTERSECT operation do?
It produces a table containing all rows common to both tables.
74
What does the DIFFERENCE (EXCEPT) operation do?
It produces a table containing all rows in one table but not in the other.
75
How can you insert multiple records into a table using a SELECT statement?
INSERT INTO TableName SELECT ...
76
What is the syntax for updating records in a table?
UPDATE TableName SET columnName1 = dataValue1 [, columnName2 = dataValue2...]
[WHERE searchCondition]
77
What happens if the WHERE clause is omitted in an UPDATE statement?
The named columns are updated for all rows in the table.
78
What must be compatible when inserting data into a table?
The data type of each item in dataValueList must be compatible with the corresponding column.
79
Fill in the blank: The SQL statement to remove data from a table is _______.
DELETE
80
What is the result of the query: SELECT * FROM STAFF WHERE Salary > ALL (SELECT Salary FROM STAFF WHERE BranchNo = 'B3');
It finds staff whose salary is larger than the salary of every member of staff at branch B3.
81
True or False: The INTERSECT operator is supported in most RDBMSs but is not supported in MS Access.
True
82
How do you insert a new row into the STAFF table supplying data for all columns?
INSERT INTO STAFF VALUES('SG16', 'John', 'Smith', 'Strand Road, Derry', '028 1234 5678', 'Manager', 'M', '1977-05-25', 18300, 'PX123456Z', 'B3');
83
What must be true for a column to allow NULL values when inserting records?
The column must have been declared as NULL when the table was created.
84
What SQL command would you use to give all staff a 5% pay increase?
UPDATE STAFF SET Salary = Salary*1.05;
85
What is the purpose of the GROUP BY clause in SQL?
It groups rows that have the same values in specified columns into summary rows.
86
What SQL command is used to update the salary of all staff members?
UPDATE STAFF SET Salary = Salary*1.05;
## Footnote
This command increases the salary of all staff by 5%
87
What command is used to give all Managers a 10% pay increase?
UPDATE STAFF SET Salary = Salary*1.1 WHERE ‘Position’ = ‘Manager’;
## Footnote
This command specifically targets staff in the Manager position
88
How do you promote David Ford to Manager and set his salary to £35,000?
UPDATE STAFF SET ‘Position’ = 'Manager’, Salary = 35000
WHERE Forename = ‘David’ AND Surname = ‘Ford’;
## Footnote
This command updates the position and salary for the specified individual
89
What SQL command is used to delete records from a table?
DELETE FROM TableName
[WHERE searchCondition]
## Footnote
'TableName' refers to the name of the table or view, and 'searchCondition' is optional
90
What happens if the searchCondition is omitted in a DELETE command?
All rows are deleted from the table
## Footnote
This is different from dropping the table itself
91
What is the command to delete all viewings related to property PG4?
DELETE FROM VIEWING
WHERE PropertyNo = ‘PG4’;
## Footnote
This command deletes specific rows based on the condition
92
What command would you use to delete all records from the Viewing table?
DELETE FROM VIEWING;
## Footnote
This deletes all records but does not remove the table itself
93
True or False: The DELETE command can be used to remove an entire table from the database.
False
## Footnote
To remove an entire table, the DROP TABLE command is used
94
What is the purpose of the SQL command DELETE?
To allow rows to be deleted from an existing table
## Footnote
This command can target specific rows or all rows based on conditions
95
Fill in the blank: The command to update a Manager's salary is __________.
UPDATE STAFF SET Salary = Salary*1.1 WHERE ‘Position’ = ‘Manager’
## Footnote
This command specifically targets Managers for a salary increase
96
What is SQL?
Structured QUERY LANGUAGE
97
What is the purpose of the JOIN operator?
To retrieve data from two or more tables based on a relationship between certain columns
98
What is a Cartesian product?
A table consisting of all possible pairs of rows from two tables
99
How is a Cartesian product produced?
When two tables are joined without using the WHERE clause
100
Define INNER JOIN
Combines rows from two tables only if values in the joined columns match
101
What happens if there is no matching field in an INNER JOIN?
Neither row appears in the query results
102
What is an OUTER JOIN?
Includes rows even if they do not have matching columns in the joined table
103
List the types of OUTER JOINS
* LEFT OUTER JOIN
* RIGHT OUTER JOIN
* FULL OUTER JOIN
104
What is the result of a LEFT OUTER JOIN?
All rows from the leftmost table are included even if there are no matches in the right table
105
What is the effect of a RIGHT OUTER JOIN?
All rows from the rightmost table are included even if there are no matches in the left table
106
What does a FULL OUTER JOIN do?
Combines the effect of both left and right outer joins
107
What is the SQL syntax for an INNER JOIN?
SELECT BRANCH_2.*, PROPERTY_FOR_RENT_2.* FROM BRANCH_2 INNER JOIN PROPERTY_FOR_RENT_2 ON BRANCH_2.City = PROPERTY_FOR_RENT_2.City
108
Fill in the blank: A LEFT OUTER JOIN includes all rows from the ______ table.
leftmost
109
True or False: The FULL OUTER JOIN is implemented in MariaDB.
False
110
What is the SQL syntax for a LEFT OUTER JOIN?
SELECT BRANCH_2.*, PROPERTY_FOR_RENT_2.* FROM BRANCH_2 LEFT OUTER JOIN PROPERTY_FOR_RENT_2 ON BRANCH_2.City = PROPERTY_FOR_RENT_2.City
111
What is the main purpose of using table aliases in SQL?
To replace long or complex table names and distinguish between attributes with the same name
112
What do NULL values in a result set from an OUTER JOIN indicate?
That there was no matching row in the other table
113
What is the SQL syntax for a FULL OUTER JOIN using UNION?
SELECT b.*, p.* FROM Branch_2 AS b LEFT JOIN Property_For_Rent_2 AS p ON b.City = p.City UNION SELECT b.*, p.* FROM Branch_2 AS b RIGHT JOIN Property_For_Rent_2 AS p ON b.City = p.City
