Section 1
Preview this deck
SELECT Query Example 2
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (282)
Section 1
(50 cards)
SELECT Query Example 2
SELECT * FROM Students WHERE first_name LIKE '%John%' ORDER BY age DESC;
SUM Function
This function is used to add the values of an attribute in a relation
Example Query
SELECT DISTINCT Attribute FROM table_name WHERE (condition) GROUP BY (Attributes) HAVING (condition) ORDER BY (Attributes) DESC or ASC;
Sequence Name
The name of the sequence.
SUM Function Example 1
SELECT SUM(age) FROM Students ORDER BY age DESC;
SUM Function Example 2
SELECT SUM(product_cost) FROM Products ORDER BY product_cost ASC;
Increment Value
Value by which the sequence will increment itself. The value can either be positive or negative.
Degree
The number of attributes in the relation is known as the degree of the relation.
Data Definition Language (DDL)
The Data Definition Language is used to define the database structure or schema. DDL is used to specify additional properties of the data.
Index Uses
1) A column contains a wide range of values 2) A column does not contain a large number of null values 3) One or more columns are frequently used together in a WHERE clause or JOIN condition.
Unique Indexes
CREATE UNIQUE INDEX index ON TABLE column
Index Avoidance
1) When the table is too small 2) The columns are not often used as a condition in the query 3) The column is updated frequently.
Tuple
Each row in the relation is known as a tuple.
Column
The column represents the set of values for a particular attribute.
Assertions
An assertion is any condition that the database must always satisfy. Domain Constraints and Integrity constraints are special forms of assertions.
SELECT Query Example 1
SELECT first_name, last_name FROM Students WHERE StudentID <=30 ORDER BY age ASC;
Insert Authorisation
Allows the insertion of data but not the modification.
Sequences Syntax
CREATE SEQUENCE (sequence_name) START WITH initial_value (0) INCREMENT BY (increment_value) (1) MINVALUE (minimum value) MAXVALUE (maximum value) CYCLE | NOCYCLE;
MySQL Binary Data Types
1) Binary max length of 8K bytes 2) VARBINARY with a max length of 8K bytes 3) image. Max length of 2.6B bytes
DISTINCT Query Example 1
SELECT DISTINCT student_address FROM Students
Aggregation Functions
Aggregation functions are used to perform some sort of mathematical operations on data values of a relation. Some mathematical functions used in SQL are: 1) COUNT 2) SUM 3) GROUP BY
Authorization
There are a couple types of database authorisations: 1) Read Authorisation 2) Insert Authorisation 3) Update Authorisation
Index Syntax
CREATE INDEX index ON TABLE column
MySQL Exact Data Types
1) int 2) smallint 3) tinyint 4) bit 5) decimal 6) numeric 7) money 8) small money 9) char with max length of 8K characters 10) VARCHAR with max length of 8K characters. 11) text with max length of 2.6B characters.
Structured Query Language (SQL)
SQL is a standard Database Language that is used to create, maintain and retrieve data from a relational database.
Referential Integrity
These are the cases where we wish to ensure that a value appears in one relation for a given set of attributes that also appear in a certain set of attributes in another relation.
Data Manipulation Language (DML)
DML statements are used for managing data in schema objects. DML comes into two types: 1) Procedural DML's 2) Declarative DML's
ALTER Query
Alters the structure of the database.
Sequences - Example Query 1
CREATE SEQUENCE my_sequence START WITH 1 INCREMENT WITH 1 MIN VALUE 0 MAX VALUE 100 Cycle;
No cycle
An exception will be thrown if the sequence exceeds its maximum value.
Example Query 3
SELECT student_address, SUM(age) FROM Students GROUP BY student_address;
MySQL Sequences
Sequences are used to generate and produce unique values on demand. A sequence is a user defined schema bound object that generates a sequence of numeric values. Sequences are frequently used in many databases because many applications require each row in a table to contain a unique value and sequences to provide an easy way to generate them. The sequence of numeric values is generated in ascending or descending order at defined intervals and can be configured to restart when it exceeds the maximum value.
DROP
Delete objects from the database.
CREATE Query
To create objects in the database.
Initial Value
Starting value from where the sequence starts. The initial value should be greater or equal to the minimum value and less than equal to the maximum value.
Update Authorisation
Allows the modification, but not the deletion.
Sequences - Example Query 2
CREATE SEQUENCE second_sequence START WITH 100 INCREMENT BY -1 MIN VALUE 1 MAX VALUE 100 cycle;
Data Definition Language (DDL)
This is used to define the structure of the database, this is done by making use of queries such as CREATE TABLE, ADD COLUMN, DROP COLUMN etc.
Cardinality
The number of tuples in a relation is known as a cardinality.
Attribute
Attributes are properties that define a relation.
Cycle
When the sequence reaches its set limit, it will then start from the beginning.
RENAME
Rename objects.
Relational Database
A relational database means the data is stored as well as retrieved in the form of relations (tables).
Removing an Index
DROP INDEX index
MySQL Indexes
An Index is a schema object. It is used by the database server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O by making use of a rapid access path method to locate data quickly. An index helps to speed up the SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and INSERT statements.
Sequences - Example Query 3
The sequence query can be used to create a table named students with columns as ID and name. CREATE TABLE Students ( ID number(10) NAME VARCHAR(20 ); INSERT INTO Students VALUES (sequence_1.nextval, 'Sabin'); INSERT INTO Students VALUES (sequence_1.nextval, 'Mary');
Read Authorisation
This allows the reading of data but not the modification of data.
COUNT Function
The count function is used to count the number of rows in a relation.
Data Manipulation Language (DML)
It is used to manipulate the data inside the database by making use of queries such as SELECT FROM and using the WHERE clause.
Domain Constraints
A domain of possible values that must be associated with every attribute, for example integer types, character types, data/time types.
Section 2
(50 cards)
Foreign Key
A foreign key is the primary key of one table in another table that is used to join two tables together to form a relationship between the tables.
NOT NULL - Example Query
CREATE TABLE Teachers ( TeacherID int(6) NOT NULL, TeacherName VARCHAR(20) NOT NULL, Address VARCHAR(20) );
Parser
During a parse call, the database performs a syntax check, semantic check and a shared pool check after converting the query into relational algebra.
GRANT
Allows specified users to perform tasks.
SAVEPOINT Query
Temporarily saves a transaction so you can rollback to that point whenever necessary.
SELECT Query
Retrieves data from the database.
UNIQUE
This constraint when it's specified with a column will tell that all the values in the column must be unique. The values in a row must not be repeated.
NOT NULL
This constraint tells that an empty value (null) cannot be stored in a column.
Database Normalisation
Database Normalisation is the process of organising the attributes of the database to reduce or eliminate data redundancy. Data redundancy will unnecessarily increase the size of the database and also produce repeated and unwanted data in places. Inconsistency issues also arise during INSERT, DELETE and UPDATE operations.
SQL - Question 4
Give the company name of the customers with orders over £500. Include the subtotal plus tax plus freight SELECT Customer.CompanyName FROM SalesOrderHeader JOIN Customer ON SalesOrderHeader.CustomerID = Customer.CustomerID WHERE SalesOrderHeader.SubTotal + SalesOrderHeader.TaxAmt + SalesOrderHeader.Freight > 500;
Difference between Primary Keys & Unique Keys
Primary Keys: 1) Used to serve as a unique identifier for each tuple in the table. 2) Cannot accept NULL (empty) values 3) Only one primary key is allowed 4) Duplicate Primary Keys are illegal. 5) Creates clustered indexes. Unique Keys: 1) Uniquely determines a row which is not a primary key. 2) Can only accept one NULL value 3) More than one unique key 4) Creates non-clustered indexes.
SQL Question 1
Show the first and email address of the customer with company name 'The bikes' SELECT FirstName, Address FROM Customer WHERE CompanyName = 'The bikes';
Transaction Control Language (TCL)
TCL commands are used to manage transactions in the database. These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions. Some TCL commands are: 1) COMMIT 2) ROLLBACK 3) SAVEPOINT
Full Query Example - Constraints
CREATE TABLE Orders ( OrderID int(10) NOT NULL, OrderNumber int(255) NOT NULL, CustomerID int(20), PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) );
Primary Key
A primary key is a unique identifier for each tuple in a database for that respective table. It can also perform integrity constraints, a primary key does not accept any duplicate values or NULL values. A primary key of one table can be a Foreign Key of another table.
SQL - WITH CLAUSE
The WITH clause allows you to give a sub-query block a name, which can be referenced in several places within the mail SQL query. The clause is used for defining a temporary relation such that the output of this relation is available and is used by the query that is associated with the WITH clause.
Super Key
The set of attributes which can unique identify a tuple is known as Super Key. Adding zero or more attributes to a candidate key generates a super key. A candidate is a super key but vice versa is not true.
SQL - Question 3
How many items with ListPrice more than 1000 have been sold? SELECT COUNT(*) AS TOTAL FROM SalesOrderDetail JOIN Product ON SalesOrderDetail.ProductID = Product.ProductID WHERE Product.ListPrice > 1000;
SQL - Question 6
Sabin Lungu made three calls on 2019-05-16. Show the date and time for each. SELECT Issue.call_date, Caller.firstName, Caller.lastName FROM Issue JOIN Caller ON(Caller.caller_ID = Issue.caller_ID) WHERE Caller.firstName = 'Sabin' AND Caller.lastName = 'Lungu' AND Issue.call_date = '2019-05-16';
SQL - Creating Roles
A role is created to setup and maintain the security model. Roles: 1) Grant or revoke privileges to users, automatically granting or revoking privileges. 2) You can either create roles or use the system roles pre-defined.
EXISTS Clause - Example
SELECT firstName, lastName FROM HotelBookings WHERE EXISTS (SELECT * FROM Customers WHERE HotelBookings.customerID = Customers.customerID)
Alternate Key
The candidate key other than the primary key is called an alternate key.
CHECK
It helps to validate the values of a column to meet a particular condition.
SQL Question 2
Show the company name for all the customers within an address in city 'Dallas' SELECT CompanyName FROM Customer JOIN CustomerAddress ON Customer.CustomerID = Customer.CustomerID JOIN Address ON CustomerAddress.CustomerID = Address.AddressID WHERE Address.City = 'Dallas';
SQL - Question 5
There are three issues that include the words 'index' and 'oracle'. Find the call date for each of these. SELECT call_date, call_ref FROM Issue WHERE detail LIKE '%index%' AND detail LIKE '%oracle%';
PRIMARY Key - Example Query
CREATE TABLE Teachers ( TeacherID int(6) NOT NULL UNIQUE, TeacherName VARCHAR(20), Address VARCHAR(20), PRIMARY KEY(TeacherID) );
COMMIT Query
This command is used to permanently save any transaction into the database.
UNIQUE - Example Query
CREATE TABLE Teachers ( TeacherID int(6) NOT NULL UNIQUE, TeacherName VARCHAR(20) NOT NULL, Address VARCHAR(20) );
Shared Pool Check
Every query has a unique hash code during execution. This type of check determines if the hash code exists, and if it exists then the database won't take any further steps for optimisation and execution.
DEFAULT
This constraint specifies a default value for the column when no value is specified by the user.
Optimiser
During this stage, the database performs a hard parse for at least one unique DML. This is a process in which multiple query execution plans for satisfying a query.
SQL Query Processing
Query processing includes the translation from high level queries to lower level queries that can be used at a physical level in file systems, query optimisation and the actual execution of queries to get the desired result.
WITH CLAUSE - Example 1
Find all the employees whose salary is > average salary of all employees. WITH tempTable(averageValue) AS (SELECT avg(Salary) FROM Employee), SELECT EmployeeID, Name, Salary FROM Employee, tempTable WHERE Employee.Salary > tempTable.averageValue;
REVOKE
Cancel previously granted or denied permissions.
WITH CLAUSE - Example 2
Find all the airlines where the total salary of all the pilots in the airline is more than the average of the total salary of all in the database. WITH totalSalary(Airline, total) AS (SELECT Airline, SUM(Salary) FROM Pilot GROUP BY Airline), airLineAverage(averageSalary) AS (SELECT AVG(total) FROM totalSalary) SELECT Airline FROM totalSalary, airlineAverage WHERE totalSalary.total > airlineAverage.averageSalary;
INSERT Query
Inserts data into a table
CHECK Query - Example
CREATE TABLE Students ( StudentID int(20) NOT NULL, StudentName VARCHAR(20) NOT NULL, StudentAge int NOT NULL CHECK (Age>=16) ); Using the CHECK constraint we can specify a condition for a field, which should
ROLLBACK Query
This command restores the database to the last committed state.
NOT EXISTS Clause - Example
SELECT CustomerForename, CustomerSurname FROM Customer WHERE NOT EXISTS (SELECT * FROM Orders WHERE Customer.customerID = Orders.orderID); This will fetch the first and last name of the customers who have not placed any orders.
DELETE Query
Deletes all of the records from a table.
SQL Constraints
Constraints are rules that can be applied on the type of data in a table. For example the limit can be specified on the type of data that can be stored in a particular column using constraints. The available constraints are: 1) NOT NULL 2) UNIQUE 3) PRIMARY KEY 4) FOREIGN KEY 5) CHECK 6) DEFAULT
UPDATE Query
Updates the existing data within a table.
Candidate Key
The minimal set of attributes which can uniquely identify a tuple is known as a Candidate Key. The value of the Candidate Key is unique and non-null for every tuple. There can be more than one candidate key in a relation.
Declarative DML
This requires a user to specify what data are needed without specifying how to get a hold of the data.
Procedural DML
This requires a user to specify what kind of data are needed and how to get the data.
Semantic Check
This determines whether the statement is meaningful or not.
WITH CLAUSE - Syntax
WITH table (averageValue) AS (SELECT AVG(attribute) FROM Table), SELECT first_attribute FROM Table WHERE Table.first_attribute > table.averageValue
Unique Keys
Unique Key constraints also identifies an individual tuple uniquely in a relation or table. A table can have more than one unique key unlike a primary key. Unique keys can accept only one NULL value for a column.
EXISTS Clause
The EXISTS condition in SQL is used to check whether the result of a nested query contains no tuples or not. The result of EXISTS is a boolean value of True or False
Data Control Language (DCL)
A DCL is a syntax similar to programming that is used to control access to data stored in a database.
Section 3
(50 cards)
Two-Tier Architecture
The two tier architecture is similar to the client-server model. The application at the client end directly communicates with the database at the server side. The server side is responsible for providing query processing and transaction management functionalities. The application on the client side establishes a connection with the server side in order to communicate with the DBMS.
SQL - LEFT JOIN
This type of JOIN returns all of the rows of the table on the left side of the join and the matching rows for the table on the right side of the join. The rows for which there is no matching row on the right side, the result for which there is no matching rows on the right side, the result set will be NULL.
SQL - Question 15
List the sales order number for the customer 'Good Toys' and 'Bike World' SELECT SalesOrderHeader.SalesOrderID, Customer.CompanyName FROM Customer LEFT JOIN SalesOrderHeader ON Customer.CustomerID = SalesOrderHeader.CustomerID WHERE CompanyName LIKE '%Good Toys%' OR CompanyName LIKE '%Bike World%';
SQL - General Functions
1) NVL() 2) NVL2() 3) COALESCE() 4) DECODE() 5) NULLIF() 6) LNNVL() 7) NANVL()
Redundant Array of Independent Disks (RAID)
RAID stands for Redundant Array of Independent Disks (RAID), is the technique used for disk organisation, reliability and performance. Both RAID 0 stands for RAID level 0 and RAID 1 for RAID 1.
SQL - Question 11
For each event in module cc29, show the day, the time and the place. SELECT event.day, event.timeOfDay, event.room FROM event WHERE event.module = 'cc29';
SQL - Question 14
Give a list of the student groups which take modules with the word 'Database' in the name SELECT student.name FROM student JOIN attends ON Student.id = attends.student JOIN event ON attends.event = event.id JOIN module ON event.module = module.ID WHERE LOWER(module.name) LIKE LOWER('%database%');
SQL - RIGHT JOIN
RIGHT JOIN is similar to LEFT JOIN, however this JOIN returns all of the rows of the table on the right side of the join that matches the rows for the table on the left side of the JOIN.
SQL - Question 18
When do they get here. List the arrival_time, first and last names for all guests due to arrive on 2019-12-09. Order by arrival_time SELECT booking.arrival_time, guest.firstName, guest.lastName FROM booking JOIN guest on (booking.guest_id = guest.id) WHERE YEAR(booking.booking_date) = '2019'; AND MONTH(booking.booking_date) = '12'; AND DAY(booking.booking_date) = '09'; ORDER BY booking.arrival_time
SQL - INNER JOIN Example 1
SELECT StudentCourse.CourseID, Student.StudentName, Student.StudentAge FROM Student INNER JOIN StudentCourse ON Student.RollNumber = StudentCourse.RollNumber;
Drill Down
In this type of operation, the less detailed data is converted into high level data. It can be done by 1) Moving down in the concept hierarchy 2) Adding a new dimension.
Key Attribute
The attribute which uniquely identifies each entity in the set is called a Key Attribute. For example a StudentID will be unique for each student.
SQL - Question 17
Guest 229, give the booking_date and the number of nights for the guest ID 229 SELECT booking_date, nights FROM booking WHERE guest_id = 229;
RAID 1
1) In RAID 1, Disk Mirroring is used. 2) RAID 1 is expensive. 3) In RAID 1, there is moderate read performance. 4) Write performance of RAID 1 is slower than RAID 0.
SQL - Question 7
There are roughly 150 calls in the system. Write a query that shows the number that have each status. SELECT Status COUNT(*) AS Volume FROM Issue GROUP BY Status;
Insertion Anomaly
This happens when the insertion of a data record is not possible without adding some additional unrelated data to the record.
First Normal Form (1NF)
If a relation contains composite or multi-valued attributes, it will violate the 1NF. A relation is in first normal form if every attribute in that relation is single valued.
3-Tier Architecture
In this type of layer, there is another layer between the client and the server. - The client does not directly communicate with the server, however it interacts with an application server with further communicates with the database system and then performs query processing and transaction management.
Database Normalisation
To reduce data redundancy. To maintain data integrity. Smaller tables, faster indexing, fast sorting, searching, updating. No accidental deleting a record.
SQL - FULL JOIN
SQL FULL JOIN Creates the result set by combining both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both of the tables.
RAID 0
In RAID 0 Disk Stripping is used, and in RAID 1, disk mirroring is used. 1) RAID 0 is a level 0. 2) In RAID 0, Disk stripping is used. 3) The cost of RAID 0 technology is low. 4) The write performance of RAID 0 is much better than RAID 1. 5) RAID 0 has a big emphasis on data accessing speed.
Enhanced Scalability
Individual connections need not be made between the client and server.
Non Trivial Functional Dependency
X-Y is a non trivial functional dependency when X is not a subset of Y. X->Y is called a completely non-trivial when X intersect Y is NULL.
3-Tier Architecture Advantages.
1) Enhanced Scalability 2) Data Integrity 3) Security
Database Management Systems | ER Models
An Entity Relationship Diagram is used to model the logical view of the database. It is used to show the relationship between tables in a database. An ER diagram consists of these components: 1) Entity 2) Entity Type 3) Entity Set
Trivial Functional Dependency
A->B is trivial when A is a subset of B. ABC -> AB ABC -> A ABC -> ABC
SQL - INNER JOIN Syntax
SELECT table1.column1, table1.column2, table2.column1 FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;
Third Normal Form (3NF)
A relation is in 3NF, if there is no Transitive Dependency for non-prime attributes that is in 2NF. A relation is in 3NF if and only if at least one condition holds: 1) X is a Super Key 2) Y is a prime attribute (each element of Y is part of some candidate key)
SQL - INNER JOIN
The INNER JOIN keyword selects all the rows from both of the tables as long as the condition satisfies.
SQL - Question 10
Give the room ID in which the event co230 takes place. SELECT event.room FROM event WHERE event.ID = 'co230';
Difference between MySQL / PL
1) SQL is a query execution or commanding language. 2) SQL is Data-oriented. 3) It is used for manipulating data (DML) 4) One statement can be executed at a time in SQL. 1) PL is a programming language 2) PL/SQL is a procedural language 3) PL/SQL used for creating applications.
SQL - JOINS
A SQL JOIN statement is used to combine data or rows from two or more tables based on a common field between them. Types of JOINS are: 1) INNER JOIN 2) RIGHT JOIN 3) LEFT JOIN 4) FULL JOIN
SQL - Question 13
Give a list of the staff and module number associated with events using room cr.132 on Wednesday, include the time each event started. SELECT staff.name, event.module, event.timeOfDay FROM staff JOIN teaches ON staff.id = teaches.staff JOIN event ON teaches.event = event.id WHERE event.room = 'cr.132' AND event.day = 'Wednesday';
Entity
An Entity can be classified as an object with a physical existence, for example a person, car, house or tree. An entity is an object of an entity type and a set of all entities is called an entity set.
Database Redundancy
Redundancy means having multiple copies of the same data in a table. This happens when the data is not normalised. Problems caused due to redundancy are: Insertion Anomaly, Deletion Anomaly and Update Anomalies.
Second Normal Form (2NF)
A relation must be in 1NF and the relation must not contain any partial dependencies. A relation is in 2NF if and only if it has No Partial Dependencies.
SQL - Question 19
Who's in room 292. Find who is staying in room 292 on 2017-04-07. Include the first name, last name and address SELECT guest.first_name, guest.last_name, guest.address FROM guest JOIN booking ON booking.guest_id = guest.id WHERE booking.room_number = 292 AND booking.booking_date = '2017-04-07';
Attributes
Attributes are the properties which defines the entity type. For example a Student_ID, Student Name and Matriculation Number are all unique for each student and therefore is classified as an attribute.
SQL - Question 8
Find the callers who have never made a call. Show the first name and last name SELECT Caller.firstName, Caller.lastName FROM Caller LEFT JOIN Issue ON (Caller.callerID = Issue.callerID) WHERE Issue.callerID IS NULL;
SQL - Question 20
How many bookings and how many nights? For guests 119 and 1990. Show the number of bookings made and the total number of nights SELECT guest.id COUNT(nights), SUM(nights) FROM booking WHERE guest.id = 119 OR guest.id = 1990 GROUP by guest.id
Functional Dependency
It is a constraint between two sets of attributes in a relation from a database. A functional dependency is denoted by an arrow (->). For example A->B, A is dependant on B.
Data Integrity
Since there is a middle layer in the 3-Tier architecture, data corruption can be avoided and removed because of this.
SQL - Question 16
List the product order number for the customers 'Sabin Lungu' 'Bob Marley' SELECT ProductOrder.OrderID, Customer.customerName FROM Customer LEFT JOIN ProductOrder ON Customer.customerID = ProductOrder.customerID WHERE customerName LIKE '%Sabin Lungu%'; OR customerName LIKE '%Bob Marley%';
Online Analytical Processing (OLAP)
OLAP stands for Online Analytical Processing. It is a software technology that allows users to analyse information from multiple database systems at the same time. Some OLAP operations are: 1) Drill Down 2) Roll Up 3) Dice 4) Slice 5) Pivot
SQL - NVL(expr1, expr2)
NVL() converts a null value to an actual value. The data types are used are date, character and number. expr1 is the source value or expression that may contain a null. expr2 is the target value for converting the null.
SQL - Question 12
List the names of the staff who teach on module SET99. SELECT DISTINCT staff.name FROM staff JOIN teaches ON staff.id = teaches.staff JOIN event on teaches.event = event.id WHERE event.module = 'SET99';
SQL - INNER JOIN Example 2
SELECT Products.ProductID, Products.ProductName, Customer.CustomerAge FROM Customer INNER JOIN Products ON Customer.CustomerID = Products.ProductID
SQL - Question 9
List the company name and the number of calls for those companies with more than 20 calls. SELECT Customer.companyName COUNT(*) FROM Customer JOIN Caller ON (Customer.companyRef = Customer.companyRef) JOIN Issue ON (Caller.callerID = Issue.callerID) GROUP BY Customer.companyName HAVING COUNT(*) > 20
Partial Dependency
If the proper subset of the candidate key determines a non-prime attribute.
SQL - LEFT JOIN Example Query
SELECT Student.StudentName, StudentCourse.CourseID FROM Student LEFT JOIN StudentCourse ON StudentCourse.RollNumber = Student.RollNumber;
Section 4
(50 cards)
Literals
Literals are constants that are used in SQL statements.
Abstraction
Beyond what is described in the service contract, services will hide the logic from the outside world.
Reusability
The logic that can potentially be reused is designed as a separate service.
SQL - COUNT(DISTINCT) Example
How many different properties were viewed in May 2018? SELECT COUNT(DISTINCT propertyNo) AS myCount FROM Viewing WHERE viewDate BETWEEN '1-May-2018' AND '31-May-2018';
Row Selection - WHERE CLAUSE
We often need to restrict the rows that are retrieve, this is done by using the WHERE Clause. The five basic search conditions or predicates are: 1) Comparison 2) Range 3) Set Membership 4) Pattern Matching 5) NULL
Derived Attributes
An attribute which can be derived from other attributes of the entity type is known as a Derived Attribute.
Composite Attribute
An attribute that is composed of many other attributes is a Composite Attribute.
Null
Tests whether a column has a null (empty/unknown) value.
Distributed DBMS
The software system that permits the management of the distributed database and makes the distribution transparent to users. A Distributed database management system consists of a single logical database that is split into fragments. Each fragment is stored on one or more computers known as "replicas". A DDBMS is required to have at least one global application. A DDBMS has the following characteristics: 1) A collection of logically related and shared data. 2) Fragments may be replicated. 3) Sites are linked by a communication network. 4) Data is split into a number of fragments.
NULL - Search condition (IS NULL / IS NOT NULL)
List the details of all viewings on property AH7 where a comment has not been supplied. SELECT clientNumber, viewData FROM Viewing WHERE propertyNo = 'AH7' AND comment IS NULL;
Autonomy
Services will have complete control over the logic they encapsulate and are not dependent upon other services.
SQL - Example 8
List all staff with a salary between 20k and 30k -> SELECT * FROM Staff WHERE Salary BETWEEN 20000 AND 30000
SQL - COUNT Function Example
How many properties cost more than £350 per month? SELECT COUNT(*) AS myCount FROM PropertyForRent WHERE theRent > 350;
Range
Testing whether the value of an expression fails within a specified range of values.
Distributed Database
A logically interrelated collection of shared data and a description of the data, that is physically distributed over a network.
Grouping Results - GROUP BY Clause
It is often useful to have subtotals in reports. This can be done by using the GROUP BY clause. A query that includes the GROUP BY clause is known as a "Grouped Query", because it groups the data from the SELECT table(s) and produces a single summary row for each group. The columns named In the GROUP BY clause are called the grouping columns. Find the number of staff working in each branch and the sum of their salary. SELECT branchNo, COUNT(staffNo) AS myStaff, SUM(salary) AS mySalary FROM Staff GROUP BY branchNo; ORDER BY branchNo; 1) SQL divides the staff into groups according to their respective branch members. 2) For each group, SQL computes the number of staff members and calculates the sum of the values in the salary column to get the total of their salary. 3) Then, finally, the result is sorted in ascending order of branch number called branchNo. Advanced Query: SELECT branchNo, (SELECT COUNT(staffNo) AS myCount FROM Staff staff WHERE staff.branchNo = branch.branchNo), (SELECT SUM(salary) AS mySum FROM Staff staff WHERE staff.branchNo = branch.branchNo) FROM Branch b ORDER BY branchNo;
SQL - Example 5
Produce a list of all monthly salaries for all the staff, showing the staff number, first and last names and salary details -> SELECT staffNo, firstName, lastName, salary/12 FROM Staff; The above query is similar to the other one, however this query returns the monthly salary.
Distributed Processing
A centralised database that can be accessed over a computer network.
SQL - Example 12 (IS NULL / IS NOT NULL)
List the details of all stolen cars with ID "STLN00" where the insurance is not paid. SELECT carID, stolenCars FROM Cars WHERE carID = 'STLN00' AND Insurance IS NULL;
Pattern Matching
Tests whether a string matches a specified pattern
Sorting Results - ORDER BY CLAUSE
Produce a list of salaries of all the staff, arranged in descending order of salary SELECT staffNo, firstName, lastName, salary FROM Staff ORDER BY Salary DESC; The "major sort key" determines the overall order of the table. If the values of the major sort key are unique, there is no need for additional keys. If a second element appears in the ORDER BY clause, it is called a minor sort key. SELECT propertyNo, type, rooms, rent FROM PropertyForRent ORDER BY type, rent ASC;
Service-Oriented Architecture (SOA)
SOA is a business-centric software architecture for developing applications that implements business processes as a set of services published at a granularity relevant to the service consumer. Services can be provided to users on the web, such as Business-2-Consumer services (B2C) or Business-2-Business Services (B2B). The main principles of SOA are: 1) Loose Coupling 2) Reusability 3) Contract 4) Abstraction 5) Autonomy 6) Stateless 7) Discoverability
Relationship Types & Sets
A Relationship type represents the association between entity types, for example a Student can be related to his/her course at University.
Loose Coupling
Service modules must be designed to interact on a loosely coupled basis.
SQL - Example 11
Pattern matching search conditions using LIKE/ NOT LIKE For this query we must search for a string 'Edinburgh' appearing somewhere within the address column of the PrivateOwner table. SQL has two pattern matching symbols: 1) The % symbol represents any sequence of zero or more characters (wildcard) 2) The _ (underscore) character represents any single character. 3) If the search string can include the pattern matching character itself, we can use an "escape character" to represent the pattern-matching character. Example: LIKE '15#%' ESCAPE '#'; SELECT ownerNo, firstName, lastName, address, telNumber FROM Staff WHERE address LIKE '%Edinburgh';
Set Membership
Test where the values of an expression equals one of a set of values.
HAVING - Clause Example 1
For each branch office with more than one staff member, find the number of staff working in each branch and sum of their salaries. SELECT branchNo, COUNT(staffNo) AS myCounter, SUM(salary) AS mySum FROM Staff GROUP BY branchNo HAVING COUNT(staffNo) > 1 ORDER BY branchNo;
SQL - Example 10
List all managers and supervisors with a salary between 50k and 70k. -> SELECT * FROM Staff WHERE position IN ('Manager', 'Supervisor) AND Salary BETWEEN 50000 AND 70000;
Multivalued Attributes
An attribute that consists of more than one value for a given entity.
SQL - Example - 7
List the addresses of all branch offices in London or Glasgow -> SELECT * FROM Branch WHERE City = 'London' OR city = 'Glasgow';
Contract
Services adhere to a communications contract that defines the information exchange and any additional service description information.
Discoverability
Services are designed in such a way that they can be found and assessed by available discovery mechanisms.
SQL - Example 1
List full details of all the staff -> SELECT * FROM Staff;
Unary Relationship
This is when there is only one entity set in a relation, this is called a Unary Relationship.
HAVING - Clause
The WHERE clause filters individual rows going into the final result table, whereas HAVING filters groups going into the final result table.
SQL - Example using WHERE
List all staff with a salary greater than 10k SELECT * FROM Staff WHERE Salary > 10000; More complex predicates such as AND, OR, NOT can be used.
SQL - COUNT and SUM Example
Find the total number of managers and the sum of their salaries. SELECT COUNT(staffNo) AS myCount, SUM(salary) as mySum FROM Staff WHERE position = 'Manager';
SQL - Example 9
List all managers and supervisors -> SELECT staffNo, firstName, lastName, position FROM Staff WHERE position IN ('Manager', 'Supervisor'); The set membership test (IN) tests whether a data value matches one of the list values. There is a negated version of IN which is NOT IN which tests to check for data values that do not lie in a specific list of values.
Subquery Example 1
List the staff who work in the branch at 163 Main St. SELECT staffNo, firstName, lastName, position FROM staff WHERE branchNo = (SELECT branchNo FROM Branch WHERE street = '163 Main St');
SQL - AGGREGATE Functions
As well as retrieving rows and columns from the database, we often want to perform some form of summation or aggregation of data, similar to the totals at the bottom of a report. The five aggregation functions are: 1) COUNT - Returns the number of values in a specified column. 2) SUM - Returns the sum of the values in a specified column. 3) MIN - Returns the smallest value in the column. 4) MAX - Returns the largest value in the column. 5) AVG - Returns the average of values in the column.
Subqueries
Subqueries in when one query is nested inside another query, usually a SELECT query inside another SELECT query. The results of this inner SELECT statement (or sub select) are used in the outer statement to help determine the contents of the final result. There are three types of subqueries: 1) A scalar subquery that returns a single column and a single row, that is, a single value. 2) A row subquery returns multiple columns, but only a single row. A row subquery can be used whenever a row value constructor is needed, typically in predicates. 3) A table subquery returns one or more columns and multiple rows. This subquery can be used in the IN predicate.
Further SQL
An SQL statement consists of reserved words and user-defined words. The reserved words must be spelled exactly as required and cannot be split across lines. DDL - Defines the database schema and structure of the database. DML - Manipulates the data created by the use of queries like: SELECT - Query the data in the database. INSERT - To insert data into the database. UPDATE - Update the data in the table DELETE - Delete the data in the database table DROP - Delete the entire database (not recommended)
SQL - MIN/MAX & AVG
Find the minimum, maximum and average staff salary. SELECT MIN(salary) AS myMin, MAX(salary) AS myMax, AVG(salary) as myAverage FROM Staff;
SQL - Example 4
To eliminate duplicates, the DISTINCT keyword is used. SELECT DISTINCT propertyNo FROM Viewing;
SQL - Example 3
List the property numbers of all properties that have been viewed. -> SELECT propertyNo FROM Viewing;
Simple Queries
The purpose of the SELECT statement is to retrieve and display data from one or more database tables. It is a powerful command, capable of performing the equivalent of the relational algebra's: Selection, Projection and JOIN operations. The * asterisk represents all of the data from the table. SELECT * FROM (table name) WHERE (condition) GROUP BY (column list) HAVING (condition) ORDER BY (column list) ASC or DESC; FROM - Specifies the table or tables to be used. WHERE - Filters the rows subject to some specified condition. GROUP BY - Forms groups of rows with the same column value. HAVING - Filters the groups subject to some condition ORDER BY - Specifies the order of the output.
Stateless
Services should not be required to manage state information, as this can affect their ability to remain loose coupled.
SQL - Example 2
Produce a list of salaries for all staff, showing only the staff number, the first and last names and the salary details. -> SELECT staffNo, firstName, lastName, salary FROM Staff; ORDER BY salary ASC;
Comparison
Compare the value of one expression to the value of another expression.
HAVING - Clause Example 2
For each hotel with more than 10 rooms, find the number of clients staying in each hotel and sum their total cost per night, and also show the clients that arrived on the 6th of March 2018 SELECT hotelNo, COUNT(clients) AS myCounter, SUM(totalCost) AS mySum FROM HotelBookings GROUP BY hotelNo HAVING COUNT(hotelNo) > 10 ORDER BY hotelNo WHERE arrival_date = "6-3-2018";
Section 5
(50 cards)
LEFT OUTER JOIN Example 1
List all branch offices and any properties that are in the same city. SELECT branch., property. FROM Branch branch LEFT JOIN PropertyForRent property ON branch.city = property.city;
AdventureWorks Database Question 7 - Medium
Where did the racing socks go? List the product name and the company name for all customers who ordered product model 'Racing Socks' SELECT Product.productName, Customer.CompanyName FROM ProductModel JOIN Product ON ProductModel.ProductModelID = Product.ProductID JOIN SalesOrderDetail ON SalesOrderDetail.ProductID = Product.ProductID JOIN SalesOrderHeader ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID JOIN Customer ON SalesOrderHeader.CustomerID = Customer.CustomerID WHERE ProductModel.name = 'Racing Socks';
Helpdesk Question 8.
For each customer show the company name, contact name, number of calls where the number of calls is < 5. SELECT a.Company_name, b.first_name, b.last_name, a.nc FROM (SELECT Customer.CompanyName, Customer.contact_id, COUNT(*) AS nc FROM Customer JOIN Caller ON Customer.company_ref = Caller.Company_ref JOIN Issue ON caller.caller_id = Caller.caller_id GROUP Customer.company_name, Customer.contact_id HAVING COUNT(*) < 5 ) AS a JOIN (SELECT * FROM Caller) AS b ON (a.Contact_id = b.caller_id);
Question 6.
A single item order is a customer order where only one item is ordered. Show the sales for the item. SELECT SalesOrderID, UnitPrice FROM SalesOrder WHERE OrderQty = 1;
Subquery Example 2
SELECT staffNo, firstName, lastName, position, salary SELECT(AVG(salary) FROM Staff) AS salDiff FROM Staff WHERE SALARY > (SELECT AVG(salary) FROM Staff;
Subquery rules
The ORDER BY clause may not be used in a subquery, however it can be used in the outermost SELECT statement.
JOIN - Example 4
SELECT staff.branchNo, staff.staffNo, COUNT(*) AS myCount FROM Staff staff, PropertyForRent property WHERE staff.staffNo = property.staffNo GROUP BY staff.branchNo, staff.staffNo ORDER BY staff.branchNo, staff.staffNo
JOIN - Example 2
For each branch office, list the staff numbers and names of staff who manage the properties and the properties that they manage. SELECT staff.branchNo, staff.staffNo, firstName, lastName, propertyNo FROM Staff staff, PropertyForRent property WHERE staff.staffNo = property.staffNo ORDER BY staff.branchNo, staff.staffNo, propertyNo;
Question 10
Show the OrderQty, Name and ListPrice of the Order made by customerID 365 SELECT OrderQty, Name, ListPrice FROM SalesOrderHeader JOIN SalesOrderDetail ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID JOIN Product ON SalesOrderDetail.ProductID = Product.ProductID WHERE CustomerID = 365;
AdventureWorks Database Question 5 - Easy
Give the company name of those customers with orders over £400. Include the subtotals plus tax plus freight SELECT Customer.CustomerName FROM SalesOrders JOIN Customer ON SalesOrders.CustomerID = Customer.CustomerID WHERE SalesOrders.SubTotal + SalesOrders.TaxAmt + SalesOrder.Freight > 400;
AdventureWorks Database Question 4 - Easy
How many items with ListPrice more than £1000 have been sold? SELECT COUNT(*) AS totalCount FROM SalesOrderDetail JOIN Product ON SalesOrderDetail.ProductID = Product.ProductID WHERE Product.ListPrice > 1000;
Neeps Question 5.
Give a list of the student groups which take modules with the word 'Database' in the name. SELECT student.name FROM student JOIN attends ON Student.id = attends.student JOIN event ON attends.event = event.id JOIN module ON event.module = module.id WHERE LOWER(module.name) LIKE LOWER('%database%');
AdventureWorks Database Question 2 - Easy
Show the company name for all customers with an address in City 'Dallas' SELECT CompanyName FROM Customer JOIN CustomerAddress ON Customer.CustomerID = CustomerAddress.CustomerID JOIN Address ON CustomerAddress.AddressID = Address.AddressID WHERE Address.City = 'Dallas';
GuestHouse Question 2 - Easy
When do they get here? List the arrival_time, first_name, last_name for all guests due to arrive on '2016'11'05', order the output by time of arrival (ASC). SELECT booking.arrival_time, guest.first_name, guest.last_name FROM booking JOIN guest on booking.booking_id = guest.guest_id WHERE YEAR(booking.booking_date) = '2016' AND MONTH(booking.booking_date) = '11' AND DAY(booking.booking_date) = '05' ORDER BY booking.arrival_time;
RIGHT OUTER JOIN Example
SELECT branch., property. FROM Branch branch RIGHT OUTER JOIN PropertyForRent property ON branch.city = property.city;
Question 12
SELECT booking.arrival_time, guest.first_name, guest.last_name FROM booking JOIN guest ON booking.booking_id = guest.id WHERE
Table Joins
To combine columns from several tables, the JOIN operations can be used. There are 4 types of JOINS: 1) INNER JOIN 2) LEFT JOIN 3) RIGHT JOIN 4) FULL JOIN.
Helpdesk Question 6.
List the company name and the number of calls for those companies with more than 18 calls. SELECT Customer.company_name, COUNT(*) As cc FROM Customer JOIN Caller ON Customer.company_ref = Caller.company_ref JOIN Issue ON Caller.caller_id = Issue.caller_id GROUP BY Customer.company_name HAVING COUNT(*) > 18;
Helpdesk Question 9.
For each shift, show the number of staff assigned. Beware that some roles may be NULL and that the same person might have been assigned to multiple roles. SELECT a.shift_date, a.shift_type, COUNT(DISTINCT role) AS cw FROM (SELECT shift_date, shift_type, Manager AS role) FROM Shift UNION ALL SELECT Shift_date, Shift_type, Operator AS role FROM Shift UNION ALL SELECT Shift_date, Shift_type, Engineer2 AS role FROM Shift) AS a GROUP BY a.Shift_date, a.Shift_type;
Question 4
Give the company name of the customers with orders over £500. Include the subtotal, tax + freight. SELECT Customer.CompanyName FROM SalesData JOIN Customer ON SalesData.CustomerID = Customer.CustomerID WHERE SalesOrderData.SubTotal + SalesOrderData.Tax + SalesOrderData.Freight > 500.
Question 5.
Find the number of left racing socks ordered by CompanyName. SELECT SUM(SalesOrderDetail.OrderQty) AS Total FROM SalesOrderDetails JOIN Product ON SalesOrderDetails.ProductID = Product.ProductID JOIN SalesOrderHeader ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID JOIN Customer ON SalesOrderHeader.CustomerID = Customer.CustomerID WHERE Product.Name = 'Racing Socks' AND Customer.CompanyName = 'Riding Cycles';
Question 8.
Show the company name for James Bob Kramer. SELECT CompanyName FROM Customer WHERE FirstName = 'James' AND MiddleName = 'Bob' AND LastName = 'Kramer';
GuestHouse Question 4 - Easy
Who is in room 100. Find who is staying in room 100 on 2016-12-03. Include the first_name, last_name and address SELECT guest.first_name, guest.last_name, guest.address FROM guest JOIN booking ON booking.guest_id = guest.guest_id WHERE booking.room_no = 100 AND booking.booking_date = '2016-12-03';
GuestHouse Question 3 - Easy
Look up the daily rates. Give the daily rate that should be paid for bookings with IDS 100, 101, 102, 190 and 20. Include the booking_id, room_type, number of occupants and the amount. SELECT booking.booking_id, booking.room_type, booking.occupantsCount, rate.amount FROM booking JOIN rate ON booking.occupants = rate.occupancy AND booking.room_type = rate.room_type WHERE booking.booking_id = 100 OR booking.booking_id = 101 OR booking.booking_id = 102
Helpdesk Question 1
There are three issues that include the words index and oracle. Find the call date for each of them. SELECT call_date, call_ref FROM Issue WHERE detail LIKE '%index%' AND detail LIKE '%Oracle%';
Neeps Question 1.
Give the room id in which the event co42001.L01 takes place in. SELECT event.room FROM event WHERE event.id = 'co42001.L01';
AdventureWorks Database Question 8 - Medium
Show the product description for culture 'FR' for product with productID 736 SELECT ProductInfo.ProductDescription FROM ProductInfo JOIN ProductModelInfo ON ProductInfo.ProductInfoID = ProductModelInfo.ProductInfoID JOIN ProductModel ON ProductModelInfo.ProductModelID = ProductModel.ProductModelID JOIN Product ON ProductModel.ProductModelID = Product.ModelID WHERE ProductModel.culture = 'FR' AND Product.productID = '736';
Question 11
Guest 1183. Give the booking_date and the number of nights for guest 1183. SELECT booking_date, nights FROM booking WHERE guest_id = 1183;
GuestHouse Question 1 - Easy
Guest 1183. Give the booking_date and the number of nights for guest 1183. SELECT booking_date, nights FROM booking WHERE guest_ID = 1183;
Neeps Question 3.
List the names of the staff who teach on module c02. SELECT DISTINCT Staff.name FROM Staff JOIN Teaches ON Staff.id = Teaches.staff JOIN Event on Teaches.event = Event.id WHERE Event.module = 'co2';
Neeps Question 2.
For each event in the module co20. Show the day, time and place. SELECT event.day, event.time, event.place FROM event WHERE event.module = 'co20';
Question 3.
How many items with ListPrice more than $1000 have been sold? SELECT COUNT(*) AS Total FROM SalesOrderData JOIN Product ON SalesOrderData.ProductID = Product.ProductID WHERE Product.ListPrice > 1000;
Cartesian product
A JOIN is a subset of more general combinations of two tables known as the Cartesian product. The Cartesian product of two tables is another table consisting of all pairs of rows form the two other tables.
Helpdesk Question 3.
There are 500 calls in the system. Write a query that shows the number that have each status. SELECT status, COUNT(*) As Volume FROM Issue GROUP BY status
Question 7.
SELECT Product.Name, Customer.CompanyName FROM ProductModel JOIN Product ON ProductModel.ProductModeID = Product.ProductModelID JOIN SalesOrderDetails ON Product.ProductID = SalesOrderDetails.ProductID JOIN SalesOrderHeader ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID JOIN Customer ON SalesOrderHeader.CustomerID = Customer.CustomerID WHERE ProductModel.Name = "The Race";
JOIN - Example 1
SELCET client.clientNo, firstName, lastName, propertyNo, comments FROM Client client, Viewing view WHERE client.clientNo = view.clientNo;
JOIN - Example 3
SELECT branch.branchNo, branch.city, staff.staffNo, firstName, lastName, propertyNo FROM Branch branch, Staff staff, PropertyForRent property WHERE branch.branchNo = staff.branchNo AND staff.staffNo = property.staffNo ORDER BY branch.branchNo, staff.staffNo, propertyNo
GuestHouse Question 5 - Easy
How many bookings, how many nights? For guests 1900 and 1928. Show the number of bookings made and the total number of nights. Your output should include the guest id and the total number of bookings and the total number of nights SELECT guest_id, COUNT(nights), SUM(nights) FROM booking WHERE guest_id = 1900 OR guest_id = 1928 GROUP by guest_id;
Question 2
Show the company name for all customers with an address in city Dallas SELECT CompanyName FROM Customer JOIN CustomerAddress ON Customer.CustomerID = CustomerAddress.CustomerID JOIN Address ON CustomerAddress.AddressID = Address.AddressID WHERE Address.City = 'Dallas';
Question 1
Show the first name and the e-mail address of customer with the company name The Bikes SELECT FirstName, Email FROM Customer WHERE CompanyName = 'The Bikes';
AdventureWorks Database Question 1 - Easy
Show the first name and the e-mail address of customer with company name 'Bike World' SELECT firstName, email FROM Customer WHERE companyName = 'Bike World';
Question 9
Show all the addresses listed for Modular Cycle Systems SELECT CompanyName, AddressType, AddressLine1 FROM Customer JOIN CustomerAddress ON Customer.CustomerID = CustomerAddress.CustomerID JOIN Address ON CustomerAddress.AddressID = Address.AddressID WHERE CompanyName = 'Modular Cycle Systems';
Neeps Question 4.
Give a list of the staff and module number associated with events using room cr.132 on Wednesday. SELECT staff.name, event.module, event.day FROM staff JOIN teaches ON staff.id = teaches.staff JOIN event ON teaches.event = event.id WHERE event.room = 'cr.132'; AND event.day = 'Wednesday';
Helpdesk Question 7.
Find the callers who have never made a call. Show the first name and last name. SELECT Caller.first_name, Caller.last_name FROM Caller LEFT JOIN Issue ON Caller.caller_id = Issue.caller_id WHERE Issue.caller_id IS NULL;
AdventureWorks Database Question 3 - Easy
Show the hotel name for all customers with an address in the City 'Edinburgh' SELECT HotelName FROM Customer JOIN CustomerAddress ON Customer.CustomerID = CustomerAddress.CustomerID JOIN Address on CustomerAddress.AddressID = Address.AddressID WHERE Address.City = 'Edinburgh';
Helpdesk Question 4.
Calls are not normally assigned to a manager but it does happen. How many calls have been assigned to staff who are at manager level? SELECT COUNT(*) As mlcc FROM Issue JOIN Staff ON Issue.assigned_to = Staff.staff_code JOIN Level ON Staff.level_code = Level.level_code WHERE Level.manager = 'Y';
Helpdesk Question 2.
Samantha Hall made three calls on 2017-08-14. Show the date and time for each. SELECT Issue.call_date, Caller.first_name, Caller.last_name FROM Issue JOIN Caller ON Issue.Caller_id = Caller.Caller_id WHERE Caller.first_name = 'Samantha' AND Caller.last_name = 'Hall' AND Issue.call_date LIKE '%2017-08-14%';
FULL JOIN - Example
Helpdesk Question 5.
Show the manager for each shift. Your output should include the shift date and type, also the first and last name of the manager. SELECT Shift.shift_date, Shift.shift_type, Staff.first_name, Staff.last_name FROM Shift JOIN Staff ON Shift.Manager = Staff.Staff_code ORDER BY Shift.shift_date
AdventureWorks Database Question 6 - Medium
A single item order is a customer where only one item is ordered. Show the SalesOrderID and the UnitPrice for every Single Item ordered. SELECT SalesOrderID, UnitPrice FROM SalesDetail WHERE OrderQty = 1;
Section 6
(32 cards)