Intro to SQL & Select statements: LMS 18 Flashcards
What does SQL stand for?
Structured Query Language
What is SQL?
(Structured Query Language?)
A database computer language
What is SQL designed for?
Designed to retrieve and manage data.
Also to create and modify DB schema, etc.
What is a Database?
An organized repository of indexed information.
(usually as a group of linked data files)
What does a Database allow?
easy retrieval, updating, analysis, and output of data.
Where is a data in a database stored?
Usually in a computer.
What form is the data in?
Graphics, reports, scripts, tables, text, etc.
Data could represent almost every kind of information.
Examples of computer apps that have data bases at their core.
Antivirus software, spreadsheets, word-processors.
Most computer apps are databases at their core.
What is a Query?
A request to the database to retrieve information.
What is a subquery?
A query within another query.
What is the outer query called?
The main query.
What is the inner query called
Subquery
Whats is executed first?
The subquery or main query?
The subquery is always executed first, and the results of the subquery are passed on to the main query.
What does DBMS stand for?
Database Management System
What does a DBMS do?
It’s a software that controls the organization, storage, retrieval, security, and integrity of data in a database.
What is a Primary Key?
Its a unique identifier of every record in the database table.
Where is the Primary Key?
Its a column in a database where each row has a unique value.
How many primary keys are in a table?
Only one primary key. No NULL values are allowed.
What is a Unique Key?
It’s a column or group of columns that together hold unique values.
A table can have more than one unique key.
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?
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.
What is a Foreign Key (SQL)?
A column or combination of columns that’s used to establish a relationship, linking the table rows to another table’s Primary Key.
What is Referential Integrity?
Refers to the relationship between tables.
What does a Foreign Key do?
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.
Is a Foreign Key unique?
A Foreign Key is usually not unique (one-to-many relation) and always points to a Primary Key.
What is a SQL Join?
An instruction to combine data from two sets of data (i.e. two tables)
What is a SQL Join Example?
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.
What are the types of Joint hat can be used to retrieve data ?
Inner Join
Right Join
Left Join
Full Join
Define Inner Join
Inner Join return rows when there is at least one match of rows between the tables.
Define Right Join
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.
Define Left Join
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.
Define Full Join
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.
What are Tables?
A Table is a set of data that is organized in a model with Columns and Rows.
What are Fields?
What are Records?
A Table has a specified number of Columns called Fields but can have any number of Rows that are called a record.
Example of Tables and Fields?
Table: Employee.
Field: Emp ID, Emp Name, Date of Birth.
Data: 201456, David, 11/15/1960.
What is a Stored Procedure?
A Stored Procedure is a function that consists of many SQL statements to access the database system that can be executed whenever required.
What does the DELETE command do?
A Delete command is used to remove rows from the table.
What can a WHERE clause be used for?
Can be used for a condiditional set of parameters.
When can Commit and Rollback be performed?
After a delete statement.
What does a TRUNCATE command do?
Removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain.
What is a Constraint
A constraint can be used to specify the limit on the data type of table.
A constraint can be specified while creating or altering the table statement.
What are samples of constraints?
- NOT NULL
- CHECK
- DEFAULT
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
What is CLAUSE?
A SQL clause is defined to limit the result set by providing the condition to the query.
Examples of Clauses
Query that has WHERE condition Query that has HAVING condition.
This usually filters some rows from the whole set of records.
What is a database schema?
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.
What does a database schema define?
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.
Structural database testing.
It focuses on testing tables and columns, schema testing, checking stored procedures and views, checking triggers, etc.
Functional testing
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.
Non-functional testing.
It includes load testing, database risk testing, stress testing, minimum system requirements, and database performance handling.
