Database SQL Flashcards
What is a database?
~a structured collection of records or data which are logically related
What is a Database Management System?
- is computer software designed for the purpose of managing databases
- typical examples of DBMSs include Oracle, Ms SQL Server and MySQL
What is a Relational Database Management System (RDBMS)?
~is a database management system (DBMS) that is based on the relational model
What is the standard language for interacting with an RDBMS?
Structured Query Language (SQL)
Give some background on SQL Server. (4)
- owned by Microsoft Corp
- first version released in 1988
- Microsoft’s philosophy of combining a high performance database with an easy-to-use interface proved to be very successful
- one of the most popular high-end relational database softwares
List the components of a relational database? (6)
Table
Row
Column
Field
Primary Key
Foreign Key
What is a table with regards to a relational database? (3)
- a basic storage structure of an RDBMS and consists of columns and rows
- represents an entity
- e.g. the employee table stores information about the employees of an organization
What is a row with regards to a relational database? (3)
- a combination of column values in a table
- identified by a primary key -also known as records
What is a column with regards to a relational database?
- a collection of one type of data in a table
- represent the attributes of an object
What is a field with regards to a relational database? (3)
- an intersection of a row and a column
- contains one data value
- there is no data in the field, the field is said to contain a NULL value
What is a primary key? (3)
- a column or a combination of columns that is used to uniquely identify each row (occurrence) in a table
- must contain a value
- cannot contain a NULL value
What is a foreign key?
- a column or set of columns that refers to a primary key in the same table or another table
- must either match a primary key or else be NULL
Give the properties of a relational database. (5)
- column values are of the same kind
- each row is unique
- the sequence of columns is insignificant
- the sequence of rows is insignificant
- each column must have a unique name
What is an ERD?
- Entity Relationship Diagram
- a design tool that graphically represents the logical relationships of entities (or objects) in order to create a database
What are the properties of an ERD?
- DBMS independent
- has many variants
- is composed of entities, attributes, and relationships
What is an entity of an ERD? (4)
- any object in the system that we want to model and store information about
- individual objects are called entities
- will become a Table in the database
- represented by rectangles (either with round or square corners)
What are ERD attributes? (3)
- property of an entity
- all data relating to an entity is held in its attributes
- appear inside ovals and are attached to their entity
How are primary keys identified in an ERD?
They are underlined.
What is a composite key?
~a key that consists of two or more attributes
What is a foreign key in an ERD and how is it represented?
- is an attribute (or group of attributes) that is the primary key to another relation
- usually shown in italics or with a wiggly underline
What is a relationship in an ERD and how is it shown?
- an association of entities
- represented on the ER diagram by a series of lines
What is cardinality?
Indicates the maximum number of relationships between the entities, therefore “many” can be explained as “one or many” or sometimes “zero, one, or many”.
What types of relationships exists in an ERD?
1 : 1 one to one relationship
1 : M one to many relationship
M : M many to many relationship
What are the steps to creating an ERD? (10)
- Identify Entities
- Find Relationships
- Draw Rough ERD
- Fill in Cardinality
- Define Primary Keys
- Draw Key-Based ERD
- Identify Attributes
- Map Attributes
- Draw fully attributed ERD
- Check Results
When do you split a relationship in an ERD? (3)
- where the m:n relationship hides an entity
- a many to many relationship in an ER model can be replaced using an intermediate entity
- the resulting ER diagram is easier to understand
What is ordinality / optionality in an ERD? (3)
- indicate whether the relationship is mandatory or optional
- can be different at each end of the relationship
- to show optionality, put a circle or
0' at theoptional end’ of the relationship
When is a relationship considered mandatory?
If the relationship is one or more.
When is a relationship optional?
If the relationship is zero or more.
List the 4 main function of Data Manipulation Language and explain what they are there for?
SELECT – to retrieve records from a table
INSERT – to add a record to a table
UPDATE – to modify the data held in a table record
DELETE – to remove a record from a table
What is the basic syntax of the SELECT function?
SELECT *
FROM tablename;
What is the basic syntax of the INSERT function?
INSERT INTO tablename
(field1, field2, … fieldn)
VALUES (value1, value2, …valuen);
Note: Text values go into single quotes; numbers have no quotes!
Give the basic syntax of the UPDATE function,
UPDATE tablename
SET field1 = value1,
field2 = value2, …
fieldn = valuen
WHERE criteria;
Note: WHERE is optional, but if omitted all data are replaced with given values!
Give the basic syntax of the DELETE function.
DELETE
FROM tablename
WHERE criteria
Note: WHERE is optional, but if not given, all records are deleted.
What operators are allowed in SQL?
– Comparison Operators
– Logical Operators
– Arithmetic Operators
– Operators used to negate conditions
Which comparison operators can be used in SQL?
= equals to
<> not equals to
> greater than
< less than
>= greater than or equal to
<= smaller than or equal to
Code: Select all products with a price greater than $100.
SELECT *
FROM Products
WHERE price > 100;
Which logical operators exist in SQL? (6)
- AND, OR, NOT
- LIKE
- BETWEEN
- IN
- IS NULL
- ALL and ANY
Code: Select all products where the price is greater than $250 and the stock quantity is greater than 5.
SELECT *
FROM Products
WHERE Price > 250 AND StockQty > 5;
How is the LIKE operator used? (3)
~used to search a character pattern without knowing the exact character value
- uses wildcard operators to compare a value to similar values
- wild card operators can be used in combinations
What wildcard operators exist and what are they used for?
% percent -> can replace zero, one or multiple characters _ underscore -> represents single numbers or characters
Code: Return all records where the surname has a “ean” in it.
SELECT *
FROM Clients
WHERE Surname LIKE ‘%ath%’
Code: Retrieve all records that have 6 digits starting with a 1 and ending with an 8.
SELECT *
FROM Clients
WHERE ClientID LIKE ‘1____8’
Code: Retrieve all records that have a 7 in 2nd position and end with a 0.
SELECT *
FROM Clients
WHERE ClientID LIKE ‘_7%0’
Why and how is the BETWEEN operator used? (5)
- used to search for values whose minimum and maximum values are given
- max and min values are included as a part of the search
- can be used with both character and numeric data types -cannot mix the data types though
- ideal for date range comparisons
Code: Retrieve all sales records with invoice dates between 01/07/2010 and 31/12/2010.
SELECT *
FROM Sales
WHERE InvoiceDate BETWEEN ‘01 Jul 2010’ AND ‘31 Dec 2010’
Note: Always use the following date format, dd mmm yyyy!
What for is the IN operator used? (3)
- searches the value from a predetermined list
- if any of the values matches, then the record will be displayed
- this can also be achieved using multiple OR operator.
What is IS NULL used for and what is a null value? (4)
- used to compare a value with a NULL value
- a space is not a null value
- for a field to have a null value, nothing must be contained within the field
- nulls are the bane of all programmers
What is an alias?
- a temporary name assigned to any field, but usually a calculated field to give the column result set a name that can be referred to
- if no alias assigned to calculated field, depending on database, column result will be either no name or a system assigned name
- no assigned name will mean that you will not be able to refer to the calculated field
Use aliases for a first name / last name combination and for price*quantity.
SELECT Firstname + ‘ ‘ + Surname AS ClientName,
(Price * 1.1) AS SalePrice
FROM Sales
List all the negate operators. (5)
Not Equal ( <> )
NOT BETWEEN
IS NOT NULL
NOT LIKE
NOT IN
What does the following code display?
SELECT * FROM Sales
WHERE InvoiceDate NOT BETWEEN
‘01 Jul 2010 ‘ AND ‘31 Dec 2010’
All invoices before 1/07/2010 and 31/12/2010.
Return all records with surnames that do not contain “ith”.
SELECT *
FROM Clients
WHERE Surname NOT LIKE ‘%ith%
Return all records with full name and hobby where the first name is not “Jill” or “Maisie”.
SELECT Surname, Firstname, Hobby
FROM Clients
WHERE Firstname NOT IN (‘Jill’, ‘Maisie’)
What does the TOP function do? (4)
- specifies that only the first set of rows will be returned from the query result
- e.g. the first 10 rows regardless of values contained within the records
- set of rows can be either a number or a percentage of the rows
- can be used in SELECT, INSERT, UPDATE, and DELETE statements
Display the top 20% of customers in the Customers table.
SELECT TOP 10 PERCENT *
FROM Customers
Display the top 8 item records in the Cars table.
SELECT TOP 10 *
FROM Customers
What does the DISTINCT function do?
- returns the unique values in a column
- repeating values will only be displayed once
You have the following list of last names. Display only the unique last names. What will be displayed?
Donovan, Price, Riddell, Smith, Connor, May, Smith, Baird, Lee, Lim, Lim
SELECT DISTINCT FirstName
FROM Customers
Donovan, Price, Riddell, Smith, Connor, May, Baird, Lee, Lim
What does the LEN() function?
- returns the length of the value in a text field
Give the syntax for the lenght function.
SELECT LEN(column_name)
FROM table_name;
What does the ISNULL function do? (Note: ISNULL not IS NULL)
- replaces a NULL with the specified replacement value
How is the ISNULL function used (syntax)?
ISNULL ( column_name , replacement_value )
FROM table_name
e.g.:
SELECT ISNULL(Price, 50))
FROM Products
- replaces all nulls in the column “Price” with 50
What does the ISNUMERIC function do?
- tests to see if a field contains a numeric value
- if the field is a valid number a 1 is returned otherwise a 0 is returned
What is the syntax of the ISNUMERIC function?
ISNUMERIC(expression)
e.g.: ISNUMERIC(‘10/11/2011’)
Result: 1
What does the following statement do?
SELECT Postcode
FROM Customers
WHERE ISNUMERIC(Postcode) < > 1
Displays all records in the Postcode column from the table Customers, that are not a valid number.
What does the ISDATE function do?
- tests to see if a field contains a date, time or datetime value
- if the field is a date, time or datetime a 1 is returned otherwise a 0 is returned
What is the syntax of the ISDATE function?
ISDATE(tested_expression)
e.g.:
SELECT ISDATE(‘2014-05-01 10:03’);
Result: 1
What do the MIN() and MAX() functions do?
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
What is the syntax of the MIN() and MAX() function?
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
Display the smallest price from the table Products, column Price. Call it “SmallestPrice”.
SELECT MIN(Price) AS SmallestPrice FROM Products;
What does the COUNT() function do and what is its syntax?
- returns the number of rows that matches a specified criteria
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
What does the AVG() function do and what is its syntax?
- returns the average value of a numeric column
SELECT AVG(column_name)
FROM table_name
WHERE condition;
What does the SUM() function do and what is its syntax?
- returns the total sum of a numeric column
SELECT SUM(column_name)
FROM table_name
WHERE condition;
What is a stored procedure?
- segment of code which contains declarative or procedural SQL statements
- can contain any SQL statement like SELECT, INSERT, UPDATE and DELETE or any SQL data definition like CREATETABLE, ALTER TABLE and procedure statements such as IF and WHILE
What are the benefits of stored procedures? (4)
- Increased performance as they are run on the serverside
- code can be reused by multiple applications
- can have their own database privileges which increases security
- through stored procs you can control how applications access and manipulate data within your database
What are the 3 parts of a stored procedure?
- the stored proc name
- parameter list
- the body which contains the SQL statements
How do you declare a new stored procedure?
USE DatabaseName
GO
CREATE PROCEDURE sp_tableName_Display
AS
BEGIN
END
What are the ways to display the result of the execution of a stored procedure?
- highlight the name of the sto Pro in the query and execute
- type EXECUTE and the name of the sto Pro in a new query window
How do you change a sto Pro?
- if the creation window is still open, replace CREATE with ALTER and change the code
- select the sto Pro in the Object Explorer, right click and go onto Modify
How do you delete a sto Pro?
- select it in the Object Explorer, right click and delete it
- write DROP PROCEDURE and the name of the sto Pro and execute it
How many parameters does a sto Pro need to have?
- can have zero, one or more parameters
- if it has more than one parameter, each one must be separated by comma
What is the naming convention for a SELECT in sto Pro?
sp_tableName_FindSomeThingBySomething
-e.g. sp_Accounts_FindAccountByUsername
What is the naming convention for an INSERT in a sto Pro?
sp_tableName_CreateSomeThing
-e.g. sp_Accounts_CreateAccount
What is the naming convention for an UPDATE in a stored procedure?
sp_tableName_UpdateSomeThing
-e.g. sp_Accounts_UpdateAccount
What is the naming convention for a DELETE in a sto Pro?
sp_tableName_DeleteSomeThing
-e.g. sp_Accounts_DeleteAccount
test
group by
order by
where…
having…
sub_query
one or more table extraction
Display all records from the table “Patients” via a sto Pro.
AS
BEGIN
SELECT *
FROM Patients
END
GO
Use a sto Pro to make it possible to insert a new patient record into the table “Patients”. Applicable columns are PatientNo and PatientFN.
Create Procedure sp_Patients_InsertNewPatient
@patientNo varchar(10),
@patientFN varchar(30)
As
Begin
Insert Into Patients(PatientNo,PatientFN)
Values(@patientNo,@patientFN)
End
GO
Use a sto Pro to update one record from the “Patients” table. Applicable columns are PatientNo and PatientFN.
- Create Procedure sp_Patients_changeNames
- @patientNo varchar(10),
- @patientFN varchar(30)
- AS
- Begin
- Update Patients
- Set PatientFN =@patientFN
- Where PatientNo= @patientNo
- End
- GO
Use sto Pro to delete a record from the table “Patients”. This only affects the PatientNo column.
Create Procedure sp_Patients_DeletePatient
@patientNo varchar(10)
As
Begin
Delete from Patients
where PatientNo=@patientNo
End
Go
What is a JOIN clause?
Is used to combine rows from two or more tables, based on a related column between them.
What is an inner join?
Returns records that have matching values in both tables.
What is a left outer join?
Return all records from the left table, and the matched records from the right table.
What is a right outer join?
Returns all records from the right table, and the matched records from the left table.
What is a full outer join?
Returns all records when there is a match in either left or right table.
What is the basic syntax of an inner join between two tables?
SELECT SourceTable1.DesiredColumn, SourceTable2.DesiredColumn, …
FROM SourceTable1
INNER JOIN SourceTable2
ON SourceTable1.ForeignKey = SourceTable2.ForeignKey
Consider two tables, Orders and Customers. Display all matching data with order ID and customer name.
SELECT SourceTable1.DesiredColumn, SourceTable2.DesiredColumn, …
FROM SourceTable1
INNER JOIN SourceTable2
ON SourceTable1.ForeignKey = SourceTable2.ForeignKey
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
Display order ID from the table Orders, customer name from the table Customers and shipper name from the table Shippers. Show only matching data.
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers
ON Orders.ShipperID = Shippers.ShipperID);
Give the syntax of a left outer join for two tables and state what data is displayed.
SELECT SourceTable1.Column1, SourceTable2.Column2
FROM SourceTable1
LEFT JOIN SourceTable2
ON SourceTable1.ForeignKey1 = SourceTable2.ForeignKey1
- the left table is the FROM table, all it’s data is shown regardless whether it matches or not
Display customer name from Customers and order ID from Orders, showing all records from Customers and only matching ones from Orders. The records should appear in order of customer names.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
Give the syntax of a right outer join.
SELECT SourceTable1.Column1, SourceTable2.Column2, …
FROM SourceTable1
RIGHT JOIN SourceTable2
ON SourceTable1.ForeignKey1 = SourceTable2.ForeignKey1
Select all records regarding order ID from the Orders table, employee first and last name from the Employees table. All data is to be shown from the Employees table (right) regardless of a match with Orders. Then, list them based on their order number.
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Give the syntax for a full join and specify what data will be shown.
SELECT SourceTable1.Column1, SourceTable2.Column2
FROM SourceTable1
FULL OUTER JOIN SourceTable2
ON SourceTable1.ForeignKey1 = SourceTable2.ForeignKey1
- all data is displayed, even data not matching in both tables
Display all data, matching or not, pertaining to customer name from Customers and order ID from Orders. Show them in alphabetical order according to their customer name.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName
What is the purpose of the GROUP BY statement?
- often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG)
- used to group the result-set by one or more columns
List the number of customers in each country. The table is called Costumers, columns are CustomerID and Country.
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
What is the ORDER BY keyword used for? (3)
- is used to sort the result-set in ascending or descending order
- records are sorted in ascending order by default (ASC)
- to sort in descending order, use the DESC keyword
How is ORDER BY used (basic syntax)?
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;
Select all customers from the Customers table and sort them in ascending order by country and in descending order by customer name.
SELECT *
FROM Customers
ORDER BY Country ASC, CustomerName DESC;
What is HAVING used for?
- replaces WHERE for aggregate functions as the latter can’t use WHERE
- aggregate functions are MIN, MAX, AVG, COUNT and SUM
List the numbers of customers for each countries. Only include countries with more than 5 customers. CustomerID and Country are columns in the table Customers.
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) \> 5;
Table Employees has a column LastName. Table Orders has a column OrderID. Check if employees Davolio and Fuller have more than 25 orders.
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = ‘Davolio’ OR LastName = ‘Fuller’
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;
