SQL Data Manipulation Flashcards
What is SQL?
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
What are the primary objectives of SQL?
- 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
Who defined the Structured English Query Language (SEQUEL)?
Donald Chamberlin at IBM San Jose Laboratory in 1974
SEQUEL was later renamed to SQL for legal reasons
When was the first SQL standard published?
In 1987 by ANSI and ISO
What are the two major components of SQL?
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
What does Data Definition Language (DDL) allow?
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
What is the purpose of Data Manipulation Language (DML)?
Allows retrieving (querying) and updating of data
Examples include SELECT, INSERT, UPDATE, DELETE
How is SQL described in terms of procedural structure?
It is non-procedural; you specify what information you require, rather than how to get it
What is the syntax for creating a table in SQL?
CREATE TABLE DatabaseName.NewTableName (ColumnName1 DataType OptionalConstraint, …)
Optional table constraints can also be included
What are the main data types available in SQL?
- Exact numeric: INT, SMALLINT, BIGINT, DECIMAL
- Approximate numeric: DOUBLE, FLOAT, REAL
- Date/Time: DATETIME, DATE, TIME, TIMESTAMP
- Character/String: CHAR, VARCHAR
What is an integrity constraint in SQL?
Rules that ensure the accuracy and consistency of data within a database
What does the NOT NULL constraint enforce?
Specifies that a column must contain a value
This is used for columns that cannot have null values
What is the purpose of the PRIMARY KEY constraint?
Ensures that each value in the column is unique and not null
What does the FOREIGN KEY constraint do?
Links a column to the primary key of another table, ensuring referential integrity
What is the syntax for altering a table to add a new column?
ALTER TABLE TableName ADD ColumnName DataType
What SQL command is used to remove a table?
DROP TABLE TableName
What is the general syntax of a SELECT statement?
SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [,…]} FROM TableName [alias] [, …] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList]
What is the purpose of the WHERE clause in a SELECT statement?
Filters rows subject to some condition
What does the ORDER BY clause do in a SQL query?
Specifies the order of the output
What is the purpose of the DISTINCT keyword in SQL?
Eliminates duplicate rows from the result set
What is a calculated field in SQL?
A field that is derived from other fields in the data set, often using arithmetic operations
Fill in the blank: The command to modify an existing column in a table is ______.
ALTER TABLE
True or False: SQL commands are case sensitive.
False
Most components of an SQL statement are case insensitive except for literal character data
What SQL command is used to eliminate duplicates in a query result?
DISTINCT
DISTINCT is applied in the SELECT statement to return only unique values.
How do you produce a list of monthly salaries for all staff?
SELECT StaffNo, Forename, Surname, Salary/12 FROM STAFF;
This query divides the annual salary by 12 to calculate the monthly salary.
What clause is used to rename a column in SQL?
AS clause
The AS clause allows you to give a column a temporary name in the result set.
What SQL command lists all staff with a salary greater than 25,000?
SELECT StaffNo, Forename, Surname, ‘Position’, Salary FROM STAFF WHERE Salary > 25000;
This query filters results using the WHERE clause to find staff earning above a specified amount.
Which SQL clause is used to find records with a salary not equal to 40,000?
WHERE Salary <> 40000
The <> operator is used to specify ‘not equal to’ in SQL.
How do you list addresses of all branch offices in London or Glasgow?
SELECT * FROM BRANCH WHERE City = ‘London’ OR City = ‘Glasgow’;
This query retrieves all columns from the BRANCH table where the city matches either London or Glasgow.
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;
This query uses compound conditions in the WHERE clause to filter results within a specific range.
What SQL command lists all managers and assistants?
SELECT StaffNo, Forename, Surname, ‘Position’ FROM STAFF WHERE Position IN (‘Manager’, ‘Assistant’);
The IN operator checks if the Position is either Manager or Assistant.
How can the IN operator be expressed without using it?
WHERE Position = ‘Manager’ OR Position = ‘Assistant’;
This alternative method uses multiple OR conditions instead of the IN operator.
What SQL command finds all owners with ‘Glasgow’ in their address?
SELECT OwnerNo, Forename, Surname, Address, TelNo FROM OWNER WHERE Address LIKE ‘%Glasgow%’;
The LIKE operator is used for pattern matching in SQL.
What do the SQL special pattern matching symbols ‘%’ and ‘_’ represent?
% represents a sequence of zero, one or more characters; _ represents a single character.
These symbols are used in conjunction with the LIKE operator for flexible pattern matching.
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;
This query uses IS NULL to check for records where a comment is not provided.
What is the negated version of IS NULL in SQL?
IS NOT NULL
This operator checks for records that do have a value in the specified column.
What is the purpose of SQL?
To manage and manipulate relational databases.
SQL stands for Structured Query Language and is essential for database operations.
What are the major components of SQL?
- Data Query Language (DQL)
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
Each component serves a specific purpose in database management.
What is the function of the SELECT statement in SQL?
To retrieve data from a database.
SELECT is one of the most commonly used commands in SQL to fetch records.
What SQL command is used to sort query results?
ORDER BY
ORDER BY sorts the result set based on one or more columns.
What SQL command is used to retrieve data from a database?
SELECT
SELECT is the primary command for querying data from a database.
What are the five aggregate functions defined by the SQL ISO standard?
- COUNT
- SUM
- AVG
- MIN
- MAX
These functions perform calculations on data and return a single value.
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
GROUP BY is often used with aggregate functions to produce summary statistics.
How do you specify a descending order in an ORDER BY clause?
ORDER BY column_name DESC
DESC keyword indicates that the results should be sorted in descending order.
What does the HAVING clause do in SQL?
Filters groups based on aggregate function results
HAVING is used after GROUP BY to apply conditions to the grouped results.
What is a sub-query?
A query nested inside another SQL query
Sub-queries can be used in SELECT, INSERT, UPDATE, and DELETE statements.
What SQL command is used to update data in a database?
UPDATE
UPDATE modifies existing records in a table.
What is the difference between WHERE and HAVING clauses?
WHERE filters rows before grouping; HAVING filters groups after aggregation
This distinction is crucial for correct SQL query results.
Fill in the blank: The SQL command used to delete records from a database is _______.
DELETE
DELETE is used to remove existing records from a table.
True or False: The ORDER BY clause can only sort by a single column.
False
ORDER BY can sort by multiple columns by separating them with commas.
What is the purpose of the SUM function in SQL?
Returns the sum of values in a specified column
SUM is often used in conjunction with GROUP BY.
What does the COUNT(*) function do?
Counts all rows in a table, including nulls
COUNT(*) returns the total number of rows, regardless of nulls or duplicates.
How do you perform a JOIN in SQL?
Include more than one table in the FROM clause and specify the JOIN condition
JOINs allow combining rows from two or more tables based on related columns.
Fill in the blank: The SQL command to create tables and set constraints is part of _______.
SQL DDL
DDL stands for Data Definition Language.
What is the purpose of the IN keyword in SQL?
Specifies multiple possible values for a column in a WHERE clause
IN is used to filter records that match any value in a list.
What does the EXISTS keyword do in SQL?
Checks for the existence of rows in a subquery
EXISTS returns true if the subquery returns one or more rows.
What is the function of the ANY keyword in SQL?
Returns true if any value in a subquery matches the condition
ANY is often used with comparison operators.
What is the purpose of the AVG function in SQL?
Returns the average of values in a specified column
AVG is used to calculate the mean of a numeric column.
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
Aliases simplify referencing tables in complex queries.
Fill in the blank: The SQL command used to combine the result sets of two or more SELECT statements is called _______.
UNION
UNION combines results from multiple SELECT statements into a single result set.
What does the MIN function do in SQL?
Returns the smallest value in a specified column
MIN can be used with numeric and date fields.
What SQL statement is used to add new rows of data to a table?
INSERT
What SQL statement is used to modify existing data in a table?
UPDATE
What SQL statement is used to remove rows of data from a table?
DELETE
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.
What is the difference between ANY and SOME in SQL?
There is no difference; SOME can be used instead of ANY.
What does the ALL operator do in SQL?
The condition is only true if it is satisfied by all values produced by the subquery.
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.
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.
What do EXISTS and NOT EXISTS check in SQL?
They check for the existence or non-existence of rows in the subquery result table.
What is the result of EXISTS if the subquery returns a non-empty set?
TRUE
What is the result of NOT EXISTS if the subquery returns an empty result table?
TRUE
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.
What does the UNION operation do?
It produces a table containing all rows in either of the two tables or both.
What does the INTERSECT operation do?
It produces a table containing all rows common to both tables.
What does the DIFFERENCE (EXCEPT) operation do?
It produces a table containing all rows in one table but not in the other.
How can you insert multiple records into a table using a SELECT statement?
INSERT INTO TableName SELECT …
What is the syntax for updating records in a table?
UPDATE TableName SET columnName1 = dataValue1 [, columnName2 = dataValue2…] [WHERE searchCondition]
What happens if the WHERE clause is omitted in an UPDATE statement?
The named columns are updated for all rows in the table.
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.
Fill in the blank: The SQL statement to remove data from a table is _______.
DELETE
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.
True or False: The INTERSECT operator is supported in most RDBMSs but is not supported in MS Access.
True
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’);
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.
What SQL command would you use to give all staff a 5% pay increase?
UPDATE STAFF SET Salary = Salary*1.05;
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.
Fill in the blank: To insert a new record by using the names of attributes, the syntax is INSERT INTO StaffName (column1, column2) VALUES (value1, value2).
What SQL command is used to update the salary of all staff members?
UPDATE STAFF SET Salary = Salary*1.05;
This command increases the salary of all staff by 5%
What command is used to give all Managers a 10% pay increase?
UPDATE STAFF SET Salary = Salary*1.1 WHERE ‘Position’ = ‘Manager’;
This command specifically targets staff in the Manager position
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’;
This command updates the position and salary for the specified individual
What SQL command is used to delete records from a table?
DELETE FROM TableName [WHERE searchCondition]
‘TableName’ refers to the name of the table or view, and ‘searchCondition’ is optional
What happens if the searchCondition is omitted in a DELETE command?
All rows are deleted from the table
This is different from dropping the table itself
What is the command to delete all viewings related to property PG4?
DELETE FROM VIEWING WHERE PropertyNo = ‘PG4’;
This command deletes specific rows based on the condition
What command would you use to delete all records from the Viewing table?
DELETE FROM VIEWING;
This deletes all records but does not remove the table itself
True or False: The DELETE command can be used to remove an entire table from the database.
False
To remove an entire table, the DROP TABLE command is used
What is the purpose of the SQL command DELETE?
To allow rows to be deleted from an existing table
This command can target specific rows or all rows based on conditions
Fill in the blank: The command to update a Manager’s salary is __________.
UPDATE STAFF SET Salary = Salary*1.1 WHERE ‘Position’ = ‘Manager’
This command specifically targets Managers for a salary increase
What is SQL?
Structured QUERY LANGUAGE
What is the purpose of the JOIN operator?
To retrieve data from two or more tables based on a relationship between certain columns
What is a Cartesian product?
A table consisting of all possible pairs of rows from two tables
How is a Cartesian product produced?
When two tables are joined without using the WHERE clause
Define INNER JOIN
Combines rows from two tables only if values in the joined columns match
What happens if there is no matching field in an INNER JOIN?
Neither row appears in the query results
What is an OUTER JOIN?
Includes rows even if they do not have matching columns in the joined table
List the types of OUTER JOINS
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
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
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
What does a FULL OUTER JOIN do?
Combines the effect of both left and right outer joins
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
Fill in the blank: A LEFT OUTER JOIN includes all rows from the ______ table.
leftmost
True or False: The FULL OUTER JOIN is implemented in MariaDB.
False
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
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
What do NULL values in a result set from an OUTER JOIN indicate?
That there was no matching row in the other table
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
