Brainscape's Knowledge GenomeTM

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


See full index

SQL > Theory > Flashcards

Theory Flashcards

1
Q

In joins, do matching fields have to have the same data definition and the same names?

A

Matching fields must have the same data definition (text, numeric, etc.) but they can have different names.

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

What are database views and what can they do?

Can they update the underlying tables?

A

Database views are virtual tables. They can:

  • provide applications with suitable data
  • be part of a database security strategy
  • give users access to the data they need
  • use data from more than one table

They may be able to update the underlying tables if they contain primary key data.

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

In an Entity-Relationship Diagram, what does a * represent?

A

A primary key

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

What is the main purpose of a database?

A

To store the persistent data for an application.

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

What’s the purpose of Middleware?

Name a type of Middleware

A

Middleware joins front ends to databases, simplifying connecting heterogenous front and back ends.

ODBC is a type of Middleware

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

What is a foreign key?

A

A copy of the primary key from another table. They’re used to link tables

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

What is a database and where are they used?

A

Structured set of data that’s organised so it can be queried.

Used in business applications, embedded software, computer games, media-rich websites

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

What does DBMS stand for and what are 5 DBMS products?

A

Database Management System

Access
MS SQL Server
MySQL
DB2
Oracle
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Does a foreign key have to be unique?

Must they link to a primary key?

A

No, foreign keys aren’t usually unique but they must link to a primary key.

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

What does SQL stand for?
What can it do? (Give the acronym)

Is it a DML or a DDL?

A 
Structure Query Language. Used to:
Create
Retrieve 
Update
Delete data   [CRUD]

It’s both a DML (Data Manipulation Language) and a DDL (Data Definition Language)

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

What are two limitations of sub-queries and where would a full join be more appropriate?

A

They won’t filter across tables and won’t bring back fields across tables.

Use a full join if all fields are needed.

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

What are views used for?

A

Storing the SQL into a consistent object.

Security.

Protecting application developers.

Providing column aliases to protect applications from database changes.

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

How do applications use virtual delete?

A
  1. Users press the delete button.
  2. UPDATE SQL runs (sets DeleteFlag to TRUE)
  3. Page is refreshed from VIEW.
  4. Updated records appear to have been deleted.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What do database administrators use to delete tables without ACTUALLY deleting records?
How is this done?

A

Virtual deletes, with views and filtering.

Adding a deleteFlag to table, updating it to 1 (TRUE)

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

What does ODBC stand for?

A

Open Database Connectivity

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

Name six data types

A
  1. Char
  2. Varchar
  3. Nvarchar
  4. Varbinary
  5. Date
  6. Datetime
17
Q

Why is backup essential to keep data safe? (4)

A

In case of:

  • physical damage to location
  • physical damage to server
  • disk failure
  • unintentional/intentional hack that removes/changes data.
18
Q

What is personal data? Name a few

A 
Information relating to an identified or identifiable person, such as
Name
Identification number
Physical 
Physiological 
Genetic
Mental
Economic
Cultural
Social identity
19
Q

Give some examples of sensitive data

A 
Race
Ethnic origin
Political opinions
Religious/philosophical beliefs
Union membership
Genetics
Biometrics
Health
Sex life/orientation
Criminal convictions/offences
20
Q

What are three types of backup?

A

Full: whole database, structure and tables

Incremental/differential: incremental copies data changed since last backup, differential is cumulative.

Transactional log: files showing all transactions.

21
Q

True/false:

A primary key is a constraint.

A

True, a primary key must be unique.

22
Q

What are two advantages of a join?

A

There doesn’t have to be all details in one table.

There isn’t duplicate data.

23
Q

What data type, varchar or nvarchar, makes the most efficient use of storage and why?

A

Varchar

It doesn’t set aside storage space if it’s not being used.

24
Q

Which data type is alphanumeric? What does this mean and what can it be used for?

A

Nvarchar is alphanumeric. It accepts numbers but won’t process them as numbers. It can be used to hold phone numbers and postcodes.

25
Q

What is varbinary(MAX) used for?

A

Used by applications which can retrieve images from a file system and insert them into a database.

26
Q

How do applications use virtual delete?

A
  1. Users press delete button.
  2. UPDATE SQL runs (sets deleteFlag to true)
  3. Page is refreshed from VIEW.
  4. Updates records appear to have been deleted.

SQL (2 decks)

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