SQL Concepts and Queries

• These notes cover SQL concepts and queries.

Disabling User Access

• To disable a user's access to a table, use REVOKE SELECT ON Customer FROM User1.

Foreign Keys

• A foreign key ensures that a value in one table exists in another, maintaining referential integrity.
• To ensure an employee can only be assigned to an existing department you apply a foreign key.

SQL Statements

• To return the country and number of orders in each country where the number of orders is less than 50: SELECT COUNT (OrderID), Country FROM Orders GROUP BY Country HAVING COUNT (orderID) < 50.

Database Backups

• A full database backup is a complete copy of all data.
• Transaction log backups do not back up all data in the database.
• Differential backups do not copy data changed before the last full backup.
• File or filegroup restores can restore a specific portion of the database.

Creating Views

• To create a view that returns an alphabetical list of game names from a table named Games: CREATE VIEW MyGame AS SELECT Name FROM Games ORDER BY Name.

Deleting Records

• To delete a customer record with CustomerID 12345: DELETE FROM Customer WHERE CustomerID = 12345.

Relational Database Table Structure

• Each value in a field in a relational database table does not need to be unique.
• Each row in a table should be unique.
• Each column name must be unique.

Removing Views

• The correct syntax to remove a view named EmployeeView is DROP VIEW EmployeeView.

Database Relationships

• Functional dependency describes the relationship when the value of one attribute (ProductID) uniquely determines the value of another (ProductCategory).

Calculating Totals

• This calculates the total points per player on team ID 1:
  • SELECT PlayerID, SUM(Points)
  • FROM PlayerStat
  • WHERE TeamID = 1
  • GROUP BY PlayerID

Data Types

• Use Decimal data type for financial functions.

Composite Primary Keys

• To relate tables like Chapter and Language you select columns from Chapter and Language tables to create a composite primary key, you should choose LanguageID and ChapterID.

Inserting data

• If you execute the statement INSERT INTO Road VALUES (1234, 36) a new row should be added to the table.

Combining Query Results

• The INTERSECT keyword combines the results of two queries, returning only the rows present in both.

Creating Reports

• To display students enrolled on/after June 1, 2020, display students who graduated in 2022, and ordering by enrollment date (most recent first):
  • SELECT * FROM students
  • WHERE enrollment_date >= '2020-06-01' OR academic_status = 'Graduated' AND graduation_date >= '2020-01-01'
  • ORDER BY enrollment_date DESC

Join Operations

• Selecting from the Employee table and the Department table gives an Equi-Join.
• Full outer joins return all rows from both tables, including unmatched rows.

Creating Views

• SQL code to create a database view of North American Mammals:
  • CREATE VIEW (dbo).(NorthAmericanMammals_View)
  • AS SELECT a.Id, a.Name
  • FROM Animal a
  • WHERE a.Class = 'Mammal' AND a.InNorthAmerica = 1

Removing Columns

• To remove a column: ALTER TABLE Customers DROP COLUMN SSN.

Altering Tables

• If you receive an error message when running the query ALTER TABLE Person ADD Prefix varchar(4) NOT NULL, the DEFAULT keyword should be used to specify a default value.

Updating Tables

• To update your table with Harry in San Francisco returning their books: UPDATE LoanedBooks SET Books = 0 WHERE (NAME = ‘Harry' AND City = 'San Francisco').

Creating Database Objects

• The proper syntax to create a database object is CREATE TABLE Student (ID INT, Name VARCHAR (100), Age INT).

Database Table Data

• The answer to this involves matching the data type to the according field
  • StudentName = VARCHAR
  • GradeLevel = INT
  • DaysAbsent = DECIMAL

Determining Movie Sample

• To determine whether Sample Movie appears only once in the Movie table you need to remove the ORDER BY clause

Calculations

• This answer involves the correct usage of SQL syntax

Creating Indexes

• The statement that creates an index is: CREATE TABLE Employee (EmployeeID INTEGER PRIMARY KEY).

Calculations on Tables

• This answer is about selecting the correct syntax of what the query asks.

Finding Products

• The correct is LIKE

Improving Search Performance

• A non-clustered index on the Category column will improve search performance.

Relationships between Tables

• A foreign key is used to ensure that each record in the Sales table has a valid associated salesperson record in the SalesPerson table.

SQL statements

• The keywords PRIMARY KEY and CONSTRAINT are valid to use in a CREATE TABLE statement

Deleting Rows

• Deleting rows in a table where the corresponding rows in the other table are automatically deleted, this is a Cascade Delete

SQL statements

• The number of rows returned by the SQL statement is 5

Statements with Tables

• The answer involves the correct SQL statement keyword
• The correct answer is the following DELETE FROM Volunteer WHERE GivenName==Tia

Chocolate

• The query that will retrieve ItemName and Price when chocolate appears in the ItemDescription column is: SELECT ItemName, Price FROM Products WHERE ItemDescription LIKE '%chocolate%';

SQL statement

• The correct answer is DELETE FROM Employee WHERE Phone IS NULL

Data Manipulation

• A constraint is a feature a relational database uses to ensure that data entered into a column is valid

Table Production

• The correct answer is UPDATE Product SET ProductCategory = 43 WHERE ProductDescription = ‘spoon’

Views

• The correct answer is unchanged

Clustered index

• Clustered index improves performance of queries that return large result sets
• And a clustered index improves performance of queries that are accessed randomly

Table Transactions

• The correct answer is No rows will be deleted from the table

More SQL

• SELECT LastName, PhoneNumber, Extension
• FROM Customers
• WHERE Phone Number IS NOT NULL
• ORDER BY LastName;

Product Number update

• You have to use UPDATE Products SET Price = Price * 1.06 WHERE ItemNumber = 1;

Stored procedure creation

• One reason to create a stored procedure is to improve performance

Database Normalisation

• You need to exclude repeating groups
• You need to exclude duplicate rows

Statements with SQL

• The correct DML (data manipulation language) statement is: INSERT INTO Employee VALUES (‘Jack Smith’);

SQL statements

• The rows get organised in no predictable order

Table Data

• The answer is NULLIF

Populating databases

• The correct answer is: SELECT * FROM Employee

Key statements

• The statement you use to remove the FOREIGN key is: ALTER TABLE

Returning flights

• SELECT FlightNumber FROM Flight
• WHERE ArrivalTime > GETDATE()
• ORDER BY ArrivalTime DESC

Keys

• You can create the table order with the statement CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY (OrderID, OrderItemID))