Intro to SQL & Select statements: LMS 18

Question 1

What does SQL stand for?

Answer 1

Structured Query Language

Question 2

What is SQL?
(Structured Query Language?)

Answer 2

A database computer language

Question 3

What is SQL designed for?

Answer 3

Designed to retrieve and manage data.
Also to create and modify DB schema, etc.

Question 4

What is a Database?

Answer 4

An organized repository of indexed information.

(usually as a group of linked data files)

Question 5

What does a Database allow?

Answer 5

easy retrieval, updating, analysis, and output of data.

Question 6

Where is a data in a database stored?

Answer 6

Usually in a computer.

Question 7

What form is the data in?

Answer 7

Graphics, reports, scripts, tables, text, etc.

Data could represent almost every kind of information.

Question 8

Examples of computer apps that have data bases at their core.

Answer 8

Antivirus software, spreadsheets, word-processors.

Most computer apps are databases at their core.

Question 9

What is a Query?

Answer 9

A request to the database to retrieve information.

Question 10

What is a subquery?

Answer 10

A query within another query.

Question 11

What is the outer query called?

Answer 11

The main query.

Question 12

What is the inner query called

Answer 12

Subquery

Question 13

Whats is executed first?
The subquery or main query?

Answer 13

The subquery is always executed first, and the results of the subquery are passed on to the main query.

Question 14

What does DBMS stand for?

Answer 14

Database Management System

Question 15

What does a DBMS do?

Answer 15

It’s a software that controls the organization, storage, retrieval, security, and integrity of data in a database.

Question 16

What is a Primary Key?

Answer 16

Its a unique identifier of every record in the database table.

Question 17

Where is the Primary Key?

Answer 17

Its a column in a database where each row has a unique value.

Question 18

How many primary keys are in a table?

Answer 18

Only one primary key. No NULL values are allowed.

Question 19

What is a Unique Key?

Answer 19

It’s a column or group of columns that together hold unique values.

A table can have more than one unique key.

Question 20

In a list of American citizens, there is a column with Social Security Numbers (SSN).
Also First and last name columns combined with phone number.

What is the Primary Key?
What is the Unique key?

Answer 20

The column with social security numbers (SSN) would be a Primary Key

The first and last name columns combined with phone number would be a Unique Key.

Question 21

What is a Foreign Key (SQL)?

Answer 21

A column or combination of columns that’s used to establish a relationship, linking the table rows to another table’s Primary Key.

Question 22

What is Referential Integrity?

Answer 22

Refers to the relationship between tables.

Question 23

What does a Foreign Key do?

Answer 23

A Foreign Key enforces the relationship between two tables via the Primary Key of the parent table and the Foreign Key of the development child of the table.

Question 24

Is a Foreign Key unique?

Answer 24

A Foreign Key is usually not unique (one-to-many relation) and always points to a Primary Key.

Question 25

What is a SQL Join?

Answer 25

An instruction to combine data from two sets of data (i.e. two tables)

Question 26

What is a SQL Join Example?

Answer 26

Let’s say we want to find all orders placed by a particular customer. We can do this by joining the customers and orders tables together using the relationship established by the customer id key.

Question 27

What are the types of Joint hat can be used to retrieve data ?

Answer 27

Inner Join Right Join Left Join Full Join

Question 28

Define Inner Join

Answer 28

Inner Join return rows when there is at least one match of rows between the tables.

Question 29

Define Right Join

Answer 29

Right Join return rows that are common between the tables and all rows of the right-hand side table. Simply, it returns all the rows from the right-hand side table even though there are no matches in the left-hand side table.

Question 30

Define Left Join

Answer 30

Left Join return rows that are common between the tables and all rows of the left-hand side table. Simply, it returns all the rows from the left-hand side table even though there are no matches in the right-hand side table.

Question 31

Define Full Join

Answer 31

Full Join return rows when there are matching rows in any one of the tables. This means that it returns all the rows from the left-hand side table and all the rows from the right-hand side table.

Question 32

What are Tables?

Answer 32

A Table is a set of data that is organized in a model with Columns and Rows.

Question 33

What are Fields? What are Records?

Answer 33

A Table has a specified number of Columns called Fields but can have any number of Rows that are called a record.

Question 34

Example of Tables and Fields?

Answer 34

Table: Employee. Field: Emp ID, Emp Name, Date of Birth. Data: 201456, David, 11/15/1960.

Question 35

What is a Stored Procedure?

Answer 35

A Stored Procedure is a function that consists of many SQL statements to access the database system that can be executed whenever required.

Question 36

What does the DELETE command do?

Answer 36

A Delete command is used to remove rows from the table.

Question 37

What can a WHERE clause be used for?

Answer 37

Can be used for a condiditional set of parameters.

Question 38

When can Commit and Rollback be performed?

Answer 38

After a delete statement.

Question 39

What does a TRUNCATE command do?

Answer 39

Removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain.

Question 40

What is a Constraint

Answer 40

A constraint can be used to specify the limit on the data type of table.

Question 41

A constraint can be specified while creating or altering the table statement. What are samples of constraints?

Answer 41

* NOT NULL * CHECK * DEFAULT * UNIQUE * PRIMARY KEY * FOREIGN KEY

Question 42

What is CLAUSE?

Answer 42

A SQL clause is defined to limit the result set by providing the condition to the query.

Question 43

Examples of Clauses

Answer 43

Query that has WHERE condition Query that has HAVING condition. This usually filters some rows from the whole set of records.

Question 44

What is a database schema?

Answer 44

Is the skeleton structure that represents the logical view of the entire database. Basically, it shows all the tables in the database and how they are connected. You can think about it as a map of the database.

Question 45

What does a database schema define?

Answer 45

Defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied to the data.

Question 46

Structural database testing.

Answer 46

It focuses on testing tables and columns, schema testing, checking stored procedures and views, checking triggers, etc.

Question 47

Functional testing

Answer 47

It involves testing the functionality of the database from the user's point of view. The most common type of functional testing is white-box and black-box testing.

Question 48

Non-functional testing.

Answer 48

It includes load testing, database risk testing, stress testing, minimum system requirements, and database performance handling.