Databases & MySQL

Relationship Types & Sets

A relationship type represents the association between entity types. For example 'Enrolled In' relationship is a relationship that exists between the Student type entity and the course.

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.

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;

Multivalued Attributes

An attribute that consists of more than one value for a given entity.

Attribute

Attributes are properties that define a relation.

Abstraction

Beyond what is described in the service contract, services will hide the logic from the outside world.

Index Syntax

CREATE INDEX index ON TABLE column

Removing an Index

DROP INDEX index

Enhanced Scalability

Individual connections need not be made between the client and server.

SQL - Example 1

List full details of all the staff -> SELECT * FROM Staff;

RENAME

Rename objects.

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';

Data Integrity

Since there is a middle layer in the 3-Tier architecture, data corruption can be avoided and removed because of this.

Range

Testing whether the value of an expression fails within a specified range of values.

Sequence Name

The name of the sequence.

Degree of Relationship Sets

The number of different entity sets participating in a relationship set is called as the degree of a relationship set. Different types of degrees are: 1) Unary Relationship 2) Binary Relationship 3) n-ary Relationship.

Cardinality

The number of tuples in a relation is known as a cardinality.

Participation Constraint

There are 2 types of constraints: 1) Total Participation 2) Partial Participation

Insertion Anomaly

This happens when the insertion of a data record is not possible without adding some additional unrelated data to the record.

Generalisation

This is the process of extracting common properties from a set of entities and create a generalised entity from it.

CREATE Query

To create objects in the database.

One-To-One (1:1)

When each entity in each entity set can take part only once in the relation, the cardinality is one to one. A male can only marry one female and vice versa.

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

3-Tier Architecture Advantages.

1) Enhanced Scalability 2) Data Integrity 3) Security

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 - General Functions

1) NVL() 2) NVL2() 3) COALESCE() 4) DECODE() 5) NULLIF() 6) LNNVL() 7) NANVL()

DBMS Advantages

1) Reduced Redundancy & Inconsistency 2) Simplified Data Access 3) Multiple Data Views 4) Data Security 5) Concurrent Access To Data 1: Data is normalized in DBMS to reduce the number of redundancy which helps in keeping the data consistent. 2: A user might need only the name of the relation, not the exact location to access the data. 3) Different views of the same data can be created to cater to the needs of different users. 4: Data Security, only authorized users are allowed to access the data in the DBMS

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.

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.

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.

Data Control Language (DCL)

A DCL is a syntax similar to programming that is used to control access to data stored in a database.

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.

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.

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

Distributed Processing

A centralised database that can be accessed over a computer network.

Distributed Database

A logically interrelated collection of shared data and a description of the data, that is physically distributed over a network.

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)

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.

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;

Trivial Functional Dependency

A->B is trivial when A is a subset of B. ABC -> AB ABC -> A ABC -> ABC

GRANT

Allows specified users to perform tasks.

ER Model

An ER Model is used to model the logical view of the system from a data perspective which consists of 3 components. 1) Entity 2) Entity Set 3) Entity Type

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

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.

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)

Assertions

An assertion is any condition that the database must always satisfy. Domain Constraints and Integrity constraints are special forms of assertions.

Multivalued Attribute

An attribute that consists of more than one value for a given entity. For example Phone_No can be more than one for a given student. In an ER diagram, a multivalued attribute is represented a double oval.

Composite Attribute

An attribute that is composed of many other attributes is a Composite Attribute.

Composite Attribute

An attribute that is composed of many other attributes. For example, an Address attribute of a student entity consists of the Street, City and Country. In ER diagrams, a composite attribute is represented by an oval comprising of other ovals.

Derived Attribute

An attribute that is derived from other attributes. For example age can be derived from the Date of Birth of a person. In an ER diagram, a derived attribute is represented by a dashed oval.

Derived Attributes

An attribute which can be derived from other attributes of the entity type is known as a Derived Attribute.

Entity

An entity may be an object that has a physical existence, a person, house, building, employee etc. An entity is an object of an entity type and a set of all entities is called an entity set.

No cycle

An exception will be thrown if the sequence exceeds its maximum value.

External Level

An external level specifies the view of the data in terms of conceptual tables.

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.

Physical Level

At the physical level, the information about the location of database objects in data store is kept.

Conceptual Level

At this level, the data is represented in the form of various database tables.

Attributes

Attributes are the properties which define the entity type, for example, StudentID, StudentName, Mobile_no etc. In an ER diagram, an attribute is represented by an OVAL.

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.

Sequences Syntax

CREATE SEQUENCE (sequence_name) START WITH initial_value (0) INCREMENT BY (increment_value) (1) MINVALUE (minimum value) MAXVALUE (maximum value) CYCLE | NOCYCLE;

Sequences - Example Query 1

CREATE SEQUENCE my_sequence START WITH 1 INCREMENT WITH 1 MIN VALUE 0 MAX VALUE 100 Cycle;

Sequences - Example Query 2

CREATE SEQUENCE second_sequence START WITH 100 INCREMENT BY -1 MIN VALUE 1 MAX VALUE 100 cycle;

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';

Comparison

Compare the value of one expression to the value of another expression.

Data Independence

Data Independence means the change of data at one level that should not affect another level.

Data Inconsistency

Data is said to be inconsistent if multiple copies of the same data does not match with each other.

Data Redundancy

Data is said to be redundant if the same data is copied at many places.

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.

DROP

Delete objects from the database.

DELETE Query

Deletes all of the records from a table.

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.

Total Participation

Each entity in the entity set must participate in the relationship. If each student must enrol in a course, the participation of the student will be total. A Total participation is shown by a double line in an ER diagram.

Tuple

Each row in the relation is known as a tuple.

Unauthorised Access

File systems may lead to unauthorised access to data. If a student gets access to his/her mark then he/she can change it in an unauthorised way.

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;

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;

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;

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;

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';

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';

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;

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;

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);

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';

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';

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";

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;

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';

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';

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%');

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%');

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;

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.

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;

SQL - Question 10

Give the room ID in which the event co230 takes place. SELECT event.room FROM event WHERE event.ID = 'co230';

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';

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;

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;

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;

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

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;

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';

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;

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;

SQL - COUNT Function Example

How many properties cost more than £350 per month? SELECT COUNT(*) AS myCount FROM PropertyForRent WHERE theRent > 350;

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.

Partial Dependency

If the proper subset of the candidate key determines a non-prime attribute.

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.

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.

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.

INSERT Query

Inserts data into a table

CHECK

It helps to validate the values of a column to meet a particular condition.

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.

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;

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.

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;

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.

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;

SQL - Example 8

List all staff with a salary between 20k and 30k -> SELECT * FROM Staff WHERE Salary BETWEEN 20000 AND 30000

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 - Example - 7

List the addresses of all branch offices in London or Glasgow -> SELECT * FROM Branch WHERE City = 'London' OR city = 'Glasgow';

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;

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

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;

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;

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';

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';

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%';

SQL - Example 3

List the property numbers of all properties that have been viewed. -> SELECT propertyNo FROM Viewing;

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%';

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');

Literals

Literals are constants that are used in SQL statements.

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

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.

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 - 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';

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.

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;

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;

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.

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 - 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.

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.

SELECT Query

Retrieves data from the database.

JOIN - Example 1

SELCET client.clientNo, firstName, lastName, propertyNo, comments FROM Client client, Viewing view WHERE client.clientNo = view.clientNo;

SELECT Query Example 2

SELECT * FROM Students WHERE first_name LIKE '%John%' ORDER BY age DESC;

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.

Example Query

SELECT DISTINCT Attribute FROM table_name WHERE (condition) GROUP BY (Attributes) HAVING (condition) ORDER BY (Attributes) DESC or ASC;

DISTINCT Query Example 1

SELECT DISTINCT student_address FROM Students

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";

SQL - INNER JOIN Example 2

SELECT Products.ProductID, Products.ProductName, Customer.CustomerAge FROM Customer INNER JOIN Products ON Customer.CustomerID = Products.ProductID

SQL - LEFT JOIN Example Query

SELECT Student.StudentName, StudentCourse.CourseID FROM Student LEFT JOIN StudentCourse ON StudentCourse.RollNumber = Student.RollNumber;

SQL - INNER JOIN Example 1

SELECT StudentCourse.CourseID, Student.StudentName, Student.StudentAge FROM Student INNER JOIN StudentCourse ON Student.RollNumber = StudentCourse.RollNumber;

Question 12

SELECT booking.arrival_time, guest.first_name, guest.last_name FROM booking JOIN guest ON booking.booking_id = guest.id WHERE

RIGHT OUTER JOIN Example

SELECT branch.*, property.* FROM Branch branch RIGHT OUTER JOIN PropertyForRent property ON branch.city = property.city;

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

EXISTS Clause - Example

SELECT firstName, lastName FROM HotelBookings WHERE EXISTS (SELECT * FROM Customers WHERE HotelBookings.customerID = Customers.customerID)

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

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;

Example Query 3

SELECT student_address, SUM(age) FROM Students GROUP BY student_address;

SQL - INNER JOIN Syntax

SELECT table1.column1, table1.column2, table2.column1 FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;

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

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.

Structured Query Language (SQL)

SQL is a standard Database Language that is used to create, maintain and retrieve data from a relational database.

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%';

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.

Loose Coupling

Service modules must be designed to interact on a loosely coupled basis.

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.

Stateless

Services should not be required to manage state information, as this can affect their ability to remain loose coupled.

Autonomy

Services will have complete control over the logic they encapsulate and are not dependent upon other services.

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;

Question 8.

Show the company name for James Bob Kramer. SELECT CompanyName FROM Customer WHERE FirstName = 'James' AND MiddleName = 'Bob' AND LastName = 'Kramer';

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';

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';

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 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 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 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 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';

Neeps Question 6.

Show the size of each of the co72010 events. Size is the total number of students attending each event. SELECT event.id, SUM(student.size) FROM student JOIN attends ON student.id = attends.student JOIN event ON attends.event = event.id WHERE event.module = 'co72010' GROUP BY event.id;

Specialisation

Specialisation is where an entity is divided into sub-entities based on their characteristics. It is a top-down approach where high level entities is specialized into two or more lower entities.

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.

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

Set Membership

Test where the values of an expression equals one of a set of values.

Null

Tests whether a column has a null (empty/unknown) value.

Pattern Matching

Tests whether a string matches a specified pattern

3-Tier Architecture

The DBMS 3-Tier Architecture divides the complete system into three tiers but also into independent modules. The 3 tiers are: 1) Physical Level. 2) Conceptual Level. 3) External Level.

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

SQL - INNER JOIN

The INNER JOIN keyword selects all the rows from both of the tables as long as the condition satisfies.

Subquery rules

The ORDER BY clause may not be used in a subquery, however it can be used in the outermost SELECT statement.

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 - 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.

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.

Key Attribute

The attribute which uniquely identifies each entity. In an ER diagram, a key attribute is denoted by an oval with an underline.

Alternate Key

The candidate key other than the primary key is called an alternate key.

Column

The column represents the set of values for a particular attribute.

COUNT Function

The count function is used to count the number of rows in a relation.

Partial Participation

The entity in the entity set may or may not participate in the relationship. If some courses are not enrolled by any of the student, the participation will be partial.

Reusability

The logic that can potentially be reused is designed as a separate service.

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.

Degree

The number of attributes in the relation is known as the degree of the relation.

Relationship Cardinality

The number of times an entity of an entity set participates in a relationship. The cardinality of a set can fall into 3 types: 1) One to one (1:1) 2) Many to one (M:1) 3) Many to many (M:M)

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.

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.

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.

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

Authorization

There are a couple types of database authorisations: 1) Read Authorisation 2) Insert Authorisation 3) Update Authorisation

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;

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%';

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%';

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.

Read Authorisation

This allows the reading of data but not the modification of data.

COMMIT Query

This command is used to permanently save any transaction into the database.

ROLLBACK Query

This command restores the database to the last committed state.

DEFAULT

This constraint specifies a default value for the column when no value is specified by the user.

Unary Relationship

This is when there is only ONE entity set participating in a relation. For example one person can only have one girlfriend.

Unary Relationship

This is when there is only one entity set in a relation, this is called a Unary Relationship.

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.

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.

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.

SQL - Example 4

To eliminate duplicates, the DISTINCT keyword is used. SELECT DISTINCT propertyNo FROM Viewing;

Database Normalisation

To reduce data redundancy. To maintain data integrity. Smaller tables, faster indexing, fast sorting, searching, updating. No accidental deleting a record.

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

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

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;

Many-to-Many (M:M)

When entities in all entity sets can take part more than once in the relationship cardinality. A student can take more than one course and one course can be taken by many students, hence many-to-many.

Many-to-One (M:1)

When entities in one set can take part only once in the relationship set and entities in the other set can take part more than once in the relationship set. For example a many people own one car only.

Cycle

When the sequence reaches its set limit, it will then start from the beginning.

Binary Relationship

When there are 2 entity sets participating in a relation. For example a student can be enrolled in 2 courses.

n-ary Relationship

When there are n entity sets participating in a relation.

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';

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';

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';

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.

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');

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.

NOT NULL

This constraint tells that an empty value (null) cannot be stored in a column.

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.

Semantic Check

This determines whether the statement is meaningful or not.

SUM Function

This function is used to add the values of an attribute in a relation

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.

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.

Domain Constraints

A domain of possible values that must be associated with every attribute, for example integer types, character types, data/time types.

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.

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.

Relational Database

A relational database means the data is stored as well as retrieved in the form of relations (tables).

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

Insert Authorisation

Allows the insertion of data but not the modification.

Update Authorisation

Allows the modification, but not the deletion.

ALTER Query

Alters the structure of the database.

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.

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) );

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

UNIQUE - Example Query

CREATE TABLE Teachers ( TeacherID int(6) NOT NULL UNIQUE, TeacherName VARCHAR(20) NOT NULL, Address VARCHAR(20) );

PRIMARY Key - Example Query

CREATE TABLE Teachers ( TeacherID int(6) NOT NULL UNIQUE, TeacherName VARCHAR(20), Address VARCHAR(20), PRIMARY KEY(TeacherID) );

NOT NULL - Example Query

CREATE TABLE Teachers ( TeacherID int(6) NOT NULL, TeacherName VARCHAR(20) NOT NULL, Address VARCHAR(20) );

Unique Indexes

CREATE UNIQUE INDEX index ON TABLE column

REVOKE

Cancel previously granted or denied permissions.

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

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

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.

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.

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;

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;

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.

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;

SELECT Query Example 1

SELECT first_name, last_name FROM Students WHERE StudentID <=30 ORDER BY age ASC;

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 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 1

Show the first and email address of the customer with company name 'The bikes' SELECT FirstName, Address FROM Customer WHERE CompanyName = 'The bikes';

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.

SAVEPOINT Query

Temporarily saves a transaction so you can rollback to that point whenever necessary.

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.

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.

UPDATE Query

Updates the existing data within a table.

Increment Value

Value by which the sequence will increment itself. The value can either be positive or negative.

WITH CLAUSE - Syntax

WITH table (averageValue) AS (SELECT AVG(attribute) FROM Table), SELECT first_attribute FROM Table WHERE Table.first_attribute > table.averageValue