Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Cert IV Programming > Database SQL > Flashcards

Database SQL Flashcards

1
Q

What is a database?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is a Database Management System?

A
  • is computer software designed for the purpose of managing databases
  • typical examples of DBMSs include Oracle, Ms SQL Server and MySQL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is a Relational Database Management System (RDBMS)?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is the standard language for interacting with an RDBMS?

A

Structured Query Language (SQL)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Give some background on SQL Server. (4)

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

List the components of a relational database? (6)

A

Table

Row

Column

Field

Primary Key

Foreign Key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a table with regards to a relational database? (3)

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is a row with regards to a relational database? (3)

A
  • a combination of column values in a table
  • identified by a primary key -also known as records
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is a column with regards to a relational database?

A
  • a collection of one type of data in a table
  • represent the attributes of an object
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is a field with regards to a relational database? (3)

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is a primary key? (3)

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is a foreign key?

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Give the properties of a relational database. (5)

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is an ERD?

A
  • Entity Relationship Diagram
  • a design tool that graphically represents the logical relationships of entities (or objects) in order to create a database
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What are the properties of an ERD?

A
  • DBMS independent
  • has many variants
  • is composed of entities, attributes, and relationships
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is an entity of an ERD? (4)

A
  • 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)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are ERD attributes? (3)

A
  • 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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How are primary keys identified in an ERD?

A

They are underlined.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is a composite key?

A

~a key that consists of two or more attributes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is a foreign key in an ERD and how is it represented?

A
  • is an attribute (or group of attributes) that is the primary key to another relation
  • usually shown in italics or with a wiggly underline
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is a relationship in an ERD and how is it shown?

A
  • an association of entities
  • represented on the ER diagram by a series of lines
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is cardinality?

A

Indicates the maximum number of relationships between the entities, therefore “many” can be explained as “one or many” or sometimes “zero, one, or many”.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What types of relationships exists in an ERD?

A

1 : 1 one to one relationship

1 : M one to many relationship

M : M many to many relationship

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A
  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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

When do you split a relationship in an ERD? (3)

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What is ordinality / optionality in an ERD? (3)

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

When is a relationship considered mandatory?

A

If the relationship is one or more.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

When is a relationship optional?

A

If the relationship is zero or more.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

List the 4 main function of Data Manipulation Language and explain what they are there for?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What is the basic syntax of the SELECT function?

A

SELECT *

FROM tablename;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What is the basic syntax of the INSERT function?

A

INSERT INTO tablename

(field1, field2, … fieldn)

VALUES (value1, value2, …valuen);

Note: Text values go into single quotes; numbers have no quotes!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Give the basic syntax of the UPDATE function,

A

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!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Give the basic syntax of the DELETE function.

A

DELETE

FROM tablename

WHERE criteria

Note: WHERE is optional, but if not given, all records are deleted.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What operators are allowed in SQL?

A

– Comparison Operators

– Logical Operators

– Arithmetic Operators

– Operators used to negate conditions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

Which comparison operators can be used in SQL?

A

= equals to

<> not equals to

> greater than

< less than

>= greater than or equal to

<= smaller than or equal to

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

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

A

SELECT *

FROM Products

WHERE price > 100;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Which logical operators exist in SQL? (6)

A
  • AND, OR, NOT
  • LIKE
  • BETWEEN
  • IN
  • IS NULL
  • ALL and ANY
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Code: Select all products where the price is greater than $250 and the stock quantity is greater than 5.

A

SELECT *

FROM Products

WHERE Price > 250 AND StockQty > 5;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

How is the LIKE operator used? (3)

A

~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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

What wildcard operators exist and what are they used for?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

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

A

SELECT *

FROM Clients

WHERE Surname LIKE ‘%ath%’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Code: Retrieve all records that have 6 digits starting with a 1 and ending with an 8.

A

SELECT *

FROM Clients

WHERE ClientID LIKE ‘1____8’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

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

A

SELECT *

FROM Clients

WHERE ClientID LIKE ‘_7%0’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

Why and how is the BETWEEN operator used? (5)

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

Code: Retrieve all sales records with invoice dates between 01/07/2010 and 31/12/2010.

A

SELECT *

FROM Sales

WHERE InvoiceDate BETWEEN ‘01 Jul 2010’ AND ‘31 Dec 2010’

Note: Always use the following date format, dd mmm yyyy!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

What for is the IN operator used? (3)

A
  • 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
Q

What is IS NULL used for and what is a null value? (4)

A
  • 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
Q

What is an alias?

A
  • 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
Q

Use aliases for a first name / last name combination and for price*quantity.

A

SELECT Firstname + ‘ ‘ + Surname AS ClientName,

(Price * 1.1) AS SalePrice

FROM Sales

50
Q

List all the negate operators. (5)

A

Not Equal ( <> )

NOT BETWEEN

IS NOT NULL

NOT LIKE

NOT IN

51
Q

What does the following code display?

SELECT * FROM Sales

WHERE InvoiceDate NOT BETWEEN

‘01 Jul 2010 ‘ AND ‘31 Dec 2010’

A

All invoices before 1/07/2010 and 31/12/2010.

52
Q

Return all records with surnames that do not contain “ith”.

A

SELECT *

FROM Clients

WHERE Surname NOT LIKE ‘%ith%

53
Q

Return all records with full name and hobby where the first name is not “Jill” or “Maisie”.

A

SELECT Surname, Firstname, Hobby

FROM Clients

WHERE Firstname NOT IN (‘Jill’, ‘Maisie’)

54
Q

What does the TOP function do? (4)

A
  • 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
Q

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

A

SELECT TOP 10 PERCENT *

FROM Customers

56
Q

Display the top 8 item records in the Cars table.

A

SELECT TOP 10 *

FROM Customers

57
Q

What does the DISTINCT function do?

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

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

A

SELECT DISTINCT FirstName

FROM Customers

Donovan, Price, Riddell, Smith, Connor, May, Baird, Lee, Lim

59
Q

What does the LEN() function?

A
  • returns the length of the value in a text field
60
Q

Give the syntax for the lenght function.

A

SELECT LEN(column_name)

FROM table_name;

61
Q

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

A
  • replaces a NULL with the specified replacement value
62
Q

How is the ISNULL function used (syntax)?

A

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
Q

What does the ISNUMERIC function do?

A
  • 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
Q

What is the syntax of the ISNUMERIC function?

A

ISNUMERIC(expression)

e.g.: ISNUMERIC(‘10/11/2011’)

Result: 1

65
Q

What does the following statement do?

SELECT Postcode

FROM Customers

WHERE ISNUMERIC(Postcode) < > 1

A

Displays all records in the Postcode column from the table Customers, that are not a valid number.

66
Q

What does the ISDATE function do?

A
  • 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
Q

What is the syntax of the ISDATE function?

A

ISDATE(tested_expression)

e.g.:

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

Result: 1

68
Q

What do the MIN() and MAX() functions do?

A

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

69
Q

What is the syntax of the MIN() and MAX() function?

A

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

70
Q

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

A 
SELECT MIN(Price) AS SmallestPrice
FROM Products;
71
Q

What does the COUNT() function do and what is its syntax?

A
  • returns the number of rows that matches a specified criteria

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

72
Q

What does the AVG() function do and what is its syntax?

A
  • returns the average value of a numeric column

SELECT AVG(column_name)
FROM table_name
WHERE condition;

73
Q

What does the SUM() function do and what is its syntax?

A
  • returns the total sum of a numeric column

SELECT SUM(column_name)
FROM table_name
WHERE condition;

74
Q

What is a stored procedure?

A
  • 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
Q

What are the benefits of stored procedures? (4)

A
  • 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
Q

What are the 3 parts of a stored procedure?

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

How do you declare a new stored procedure?

A

USE DatabaseName

GO

CREATE PROCEDURE sp_tableName_Display

AS

BEGIN

END

78
Q

What are the ways to display the result of the execution of a stored procedure?

A
  • 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
Q

How do you change a sto Pro?

A
  • 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
Q

How do you delete a sto Pro?

A
  • 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
Q

How many parameters does a sto Pro need to have?

A
  • can have zero, one or more parameters
  • if it has more than one parameter, each one must be separated by comma
82
Q

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

A

sp_tableName_FindSomeThingBySomething

-e.g. sp_Accounts_FindAccountByUsername

83
Q

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

A

sp_tableName_CreateSomeThing

-e.g. sp_Accounts_CreateAccount

84
Q

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

A

sp_tableName_UpdateSomeThing

-e.g. sp_Accounts_UpdateAccount

85
Q

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

A

sp_tableName_DeleteSomeThing

-e.g. sp_Accounts_DeleteAccount

86
Q

test

A

group by

order by

where…

having…
sub_query

one or more table extraction

87
Q

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

A

AS

BEGIN

SELECT *

FROM Patients

END

GO

88
Q

Use a sto Pro to make it possible to insert a new patient record into the table “Patients”. Applicable columns are PatientNo and PatientFN.

A

Create Procedure sp_Patients_InsertNewPatient
@patientNo varchar(10),
@patientFN varchar(30)

As
Begin
Insert Into Patients(PatientNo,PatientFN)
Values(@patientNo,@patientFN)

End
GO

89
Q

Use a sto Pro to update one record from the “Patients” table. Applicable columns are PatientNo and PatientFN.

A
  • Create Procedure sp_Patients_changeNames
  • @patientNo varchar(10),
  • @patientFN varchar(30)
  • AS
  • Begin
  • Update Patients
  • Set PatientFN =@patientFN
  • Where PatientNo= @patientNo
  • End
  • GO
90
Q

Use sto Pro to delete a record from the table “Patients”. This only affects the PatientNo column.

A

Create Procedure sp_Patients_DeletePatient

@patientNo varchar(10)

As

Begin

Delete from Patients

where PatientNo=@patientNo

End

Go

91
Q

What is a JOIN clause?

A

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

92
Q

What is an inner join?

A

Returns records that have matching values in both tables.

93
Q

What is a left outer join?

A

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

94
Q

What is a right outer join?

A

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

95
Q

What is a full outer join?

A

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

96
Q

What is the basic syntax of an inner join between two tables?

A

SELECT SourceTable1.DesiredColumn, SourceTable2.DesiredColumn, …

FROM SourceTable1

INNER JOIN SourceTable2

ON SourceTable1.ForeignKey = SourceTable2.ForeignKey

97
Q

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

A

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers

ON Orders.CustomerID = Customers.CustomerID;

98
Q

Display order ID from the table Orders, customer name from the table Customers and shipper name from the table Shippers. Show only matching data.

A

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
Q

Give the syntax of a left outer join for two tables and state what data is displayed.

A

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
Q

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.

A

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

101
Q

Give the syntax of a right outer join.

A

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

102
Q

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.

A

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

103
Q

Give the syntax for a full join and specify what data will be shown.

A

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
Q

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.

A

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

FULL OUTER JOIN Orders

ON Customers.CustomerID=Orders.CustomerID

ORDER BY Customers.CustomerName

105
Q

What is the purpose of the GROUP BY statement?

A
  • often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG)
  • used to group the result-set by one or more columns
106
Q

List the number of customers in each country. The table is called Costumers, columns are CustomerID and Country.

A 
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
107
Q

What is the ORDER BY keyword used for? (3)

A
  • 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
Q

How is ORDER BY used (basic syntax)?

A

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

109
Q

Select all customers from the Customers table and sort them in ascending order by country and in descending order by customer name.

A

SELECT *

FROM Customers

ORDER BY Country ASC, CustomerName DESC;

110
Q

What is HAVING used for?

A
  • replaces WHERE for aggregate functions as the latter can’t use WHERE
  • aggregate functions are MIN, MAX, AVG, COUNT and SUM
111
Q

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.

A 
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) \> 5;
112
Q

Table Employees has a column LastName. Table Orders has a column OrderID. Check if employees Davolio and Fuller have more than 25 orders.

A

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
Q
A

Cert IV Programming (7 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions