Database SQL Flashcards Preview

Cert IV Programming > Database SQL > Flashcards

Flashcards in Database SQL Deck (113):
1

What is a database?

~a structured collection of records or data which are logically related

2

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

3

What is a Relational Database Management System (RDBMS)?

~is a database management system (DBMS) that is based on the relational model

4

What is the standard language for interacting with an RDBMS?

Structured Query Language (SQL)

5

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

6

List the components of a relational database? (6)

Table

Row

Column

Field

Primary Key

Foreign Key

7

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

8

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

9

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

10

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

11

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

12

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

13

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

14

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

15

What are the properties of an ERD?

  • DBMS independent
  • has many variants
  • is composed of entities, attributes, and relationships

16

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)

17

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

18

How are primary keys identified in an ERD?

They are underlined.

19

What is a composite key?

~a key that consists of two or more attributes

20

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

21

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

22

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

23

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

24

What are the steps to creating an ERD? (10)

  1. Identify Entities
  2. Find Relationships
  3. Draw Rough ERD
  4. Fill in Cardinality
  5. Define Primary Keys
  6. Draw Key-Based ERD
  7. Identify Attributes
  8. Map Attributes
  9. Draw fully attributed ERD
  10. Check Results

25

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

26

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 the `optional end' of the relationship

27

When is a relationship considered mandatory?

If the relationship is one or more.

28

When is a relationship optional?

If the relationship is zero or more.

29

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

30

What is the basic syntax of the SELECT function?

SELECT *

FROM tablename;

31

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!

32

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!

33

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.

34

What operators are allowed in SQL?

– Comparison Operators

– Logical Operators

– Arithmetic Operators

– Operators used to negate conditions

35

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

36

Code: Select all products with a price greater than $100.

SELECT *

FROM Products

WHERE price > 100;

37

Which logical operators exist in SQL? (6)

• AND, OR, NOT

• LIKE

• BETWEEN

• IN

• IS NULL

• ALL and ANY

38

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;

39

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

40

What wildcard operators exist and what are they used for?

% percent -> can replace zero, one or multiple characters _ underscore -> represents single numbers or characters

41

Code: Return all records where the surname has a "ean" in it.

SELECT *

FROM Clients

WHERE Surname LIKE '%ath%'

42

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'

43

Code: Retrieve all records that have a 7 in 2nd position and end with a 0.

SELECT *

FROM Clients

WHERE ClientID  LIKE '_7%0'

44

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

45

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!

46

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.

47

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

48

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

49

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

50

List all the negate operators. (5)

Not Equal ( <> )

NOT BETWEEN

IS NOT NULL

NOT LIKE

NOT IN

51

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. 

52

Return all records with surnames that do not contain "ith".

SELECT *

FROM Clients

WHERE Surname NOT LIKE '%ith%

53

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

54

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

55

Display the top 20% of customers in the Customers table.

SELECT TOP 10 PERCENT *

FROM Customers

56

Display the top 8 item records in the Cars table.

SELECT TOP 10 *

FROM Customers

57

What does the DISTINCT function do?

  • returns the unique values in a column
  • repeating values will only be displayed once

58

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

59

What does the LEN() function?

  •  returns the length of the value in a text field

60

Give the syntax for the lenght function.

SELECT LEN(column_name)

FROM table_name;

61

What does the ISNULL function do? (Note: ISNULL not IS NULL)

  • replaces a NULL with the specified replacement value

62

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

63

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

64

What is the syntax of the ISNUMERIC function?

ISNUMERIC(expression)

 

e.g.: ISNUMERIC('10/11/2011')

Result: 1

 

65

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.

66

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

67

What is the syntax of the ISDATE function?

ISDATE(tested_expression)

 

e.g.:

SELECT ISDATE('2014-05-01 10:03');

Result: 1

68

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.

69

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;

70

Display the smallest price from the table Products, column Price. Call it "SmallestPrice".

SELECT MIN(Price) AS SmallestPrice
FROM Products;

71

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;

72

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;

73

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;

74

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

75

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

76

What are the 3 parts of a stored procedure?

  • the stored proc name
  • parameter list
  • the body which contains the SQL statements

77

How do you declare a new stored procedure?

USE DatabaseName

GO

 

CREATE PROCEDURE sp_tableName_Display

AS

BEGIN

 

END

78

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

79

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

80

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

81

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

82

What is the naming convention for a SELECT in sto Pro?

sp_tableName_FindSomeThingBySomething

-e.g. sp_Accounts_FindAccountByUsername

83

What is the naming convention for an INSERT in a sto Pro?

sp_tableName_CreateSomeThing


-e.g. sp_Accounts_CreateAccount

84

What is the naming convention for an UPDATE in a stored procedure?

sp_tableName_UpdateSomeThing


-e.g. sp_Accounts_UpdateAccount

85

What is the naming convention for a DELETE in a sto Pro?

sp_tableName_DeleteSomeThing


-e.g. sp_Accounts_DeleteAccount

86

test

group by

order by

where...

having...
sub_query

one or more table extraction

87

Display all records from the table "Patients" via a sto Pro.

AS

BEGIN

SELECT *

FROM Patients

END

GO

 

88

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

89

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

90

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

91

What is a JOIN clause?

Is used to combine rows from two or more tables, based on a related column between them.

92

What is an inner join?

Returns records that have matching values in both tables.

A image thumb
93

What is a left outer join?

Return all records from the left table, and the matched records from the right table.

 

 

A image thumb
94

What is a right outer join?

Returns all records from the right table, and the matched records from the left table.

 

 

A image thumb
95

What is a full outer join?

Returns all records when there is a match in either left or right table.

 

A image thumb
96

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

97

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;

98

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

99

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

100

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;

101

Give the syntax of a right outer join.

SELECT SourceTable1.Column1, SourceTable2.Column2, ...
FROM SourceTable1
RIGHT JOIN SourceTable2
ON SourceTable1.ForeignKey1 = SourceTable2.ForeignKey1
 

        

102

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;
 

103

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

104

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

105

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

106

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;

107

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

108

How is ORDER BY used (basic syntax)?

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

109

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;

110

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

111

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;

112

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;

113