SQL Part 1 Flashcards
Explain what SQL is.
SQL stands for Structured Query Language. SQL lets you access and manipulate databases. SQL is the standard language for Relational Database System
What are some SQL databases?
Oracle.
PostgreSQL.
Microsoft SQL Server.
MariaDB.
IBM Db2.
Amazon Aurora.
Google Cloud Spanner.
What are the 5 sublanguages of SQL? Which commands correspond to them?
DDL
(Data Definition Language),
DML
(Data Manipulation Language),
DQL/DRL
(Data Query Language),
DCL
(Data Control Language),
TCL
(Transaction Control Language)
Which commands correspond to them?
DML
INSERT, UPDATE, DELETE
DDL
CREATE, DROP, RENAME, TRUNCATE, ALTER
DCL
GRANT, REVOKE
TCL
COMMIT, ROLLBACK, SAVEPOINT
DQL
SELECT
What is the difference between DELETE, DROP, and TRUNCATE commands?
The DELETE command deletes one or more existing records from the table in the database
The DROP Command drops the complete table from the database.
The TRUNCATE Command deletes all the rows from the existing table, leaving the row with the column names.
What are some SQL clauses you can use with SELECT statements?
SELECT – the columns in the result set.
FROM – names the base table(s) from which results will be retrieved.
WHERE – specifies any conditions for the results set (filter)
ORDER BY – sets how the result set will be ordered.
LIMIT – sets the number of rows to be returned.
What is the difference between WHERE and HAVING?
WHERE Clause is used to filter the records from the table based on the specified condition.
HAVING Clause is used to filter record from the groups based on the specified condition.
Explain what the ORDER BY and GROUP BY clauses do
The Group By clause is used to group data based on the same value in a specific column.
The ORDER BY clause, on the other hand, sorts the result and shows it in ascending or descending order
Explain the concept of relational integrity
the set of rules that can be used to maintain the data integrity during an insert, delete and update operations into a table. These constraints are checked in the database before performing any operation such as insertion, deletion, and updation on the tables
List the integrity constraints
entity integrity, referential integrity and domain integrity
Define the word “schema”
a schema is a list of logical structures of data
What is a candidate key?
A candidate key is a specific type of field in a relational database that can identify each unique record independently of any other data.
What is a surrogate key?
Surrogate Key in SQL Server is a unique identifier for each row in the table.
It is the sequential number outside of the database that is made available to the user and the application or it acts as an object that is present in the database but is not visible to the user or application.
What conditions lead to orphan records?
An orphan record is when a child record with a foreign key points to a parent record of a primary key field that no longer exists.
What are some SQL data types?
- Numeric data types such as int, tinyint, bigint, float, real, etc.
- Date and Time data types such as Date, Time, Datetime, etc.
- Character and String data types such as char, varchar, text, etc.
- Unicode character string data types, for example nchar, nvarchar, ntext, etc.
- Binary data types such as binary, varbinary, etc.
- Miscellaneous data types - clob, blob, xml, cursor, table, etc.
