SQL Flashcards
What is the left outer join and the right outer join in SQL?
Left outer join returns entire results from the left table along with the matched records from the right table. Right outer join is the opposite of this
What does it mean to truncate data?
To delete the data but not the table itself
What are primary keys and foreign keys?
Primary key - the unique identifier for each row of data stored in a database
Foreign key - a column or a group of columns in a relational database that describes how two tables are linked to each other
What is a table in SQL?
Tables are database objects that contain all the data in a database.
What is a database?
A SQL database is a collection of tables that stores a specific set of structured data
What are the different types of join?
Inner join, left outer join, right outer join, full outer join
What is data normalisation?
Normalization is the process to eliminate data redundancy and enhance data integrity in the table. Normalization also helps to organize the data in the database. It is a multi-step process that sets the data into tabular form and removes the duplicated data from the relational tables
What is the difference between unique key, foreign key and primary key?
primary - cannot be NULL, every row has its own primary key, used to relate tables together
foreign - a field in a table that’s a unique key in another table, used to link relational databases together
unique - can be NULL, tables may have more than one unique constraint, something like a student ID number
How can you fetch data from a table?
By using the SELECT statement
How can you use a join table to fetch data from multiple tables?
SELECT *
FROM
JOIN ON primary/foreign keys
What are SQL triggers?
A trigger is a special type of procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements
What command allows you to remove an entire table using SQL?
DROP command
What is big data?
The definition of big data is data that contains greater variety, arriving in increasing volumes and with more velocity. This is also known as the three Vs. (VARIETY, VOLUME, VELOCITY)
Put simply, big data is larger, more complex data sets, especially from new data sources. These data sets are so voluminous that traditional data processing software just can’t manage them. But these massive volumes of data can be used to address business problems you wouldn’t have been able to tackle before.
What is artificial intelligence?
Artificial intelligence leverages computers and machines to mimic the problem-solving and decision-making capabilities of the human mind.
What is cloud computing?
Cloud computing is the on-demand delivery of IT resources over the Internet with pay-as-you-go pricing. Instead of buying, owning, and maintaining physical data centers and servers, you can access technology services, such as computing power, storage, and databases, on an as-needed basis from a cloud provider like Amazon Web Services (AWS).
Benefits - scalability, cost savings, fast global deployment
What are the main advantages of SQL?
- quick and efficient to query large amounts of data
- user friendly due to use of kws such as SELECT, UPDATE, etc.
- standardised with good documentation
- platform independent (mac/windows/etc)
What are the SET operations in SQL?
- UNION
Combines the result of two or more SELECT statements and removes any duplicates - UNION ALL
Combines the result of two or more SELECT statements and does not remove duplicates - INTERSECT
Returns the common results from two SELECT statements - MINUS
Returns the results from the first of two SELECT statements that don’t also exist in the second SELECT statement
What are the joins in SQL?
A join in SQL is used to combine rows from two or more tables, based on a common column. Types:
- inner join
returns records that have matching values in both tables
- left outer join
returns all records from the left table and matching records from the right table
- right outer join
returns all records from the right table and matching records from the left table
- full outer join
returns all records from both tables
Difference between primary key and unique key
Primary key will not accept NULL values whereas Unique key can accept NULL values.
A table can have only one primary key whereas there can be multiple unique key on a table.
A Clustered index automatically created when a primary key is defined whereas Unique key generates the non-clustered index.
What is a master key?
The database master key (DMK) is the base encryption key inside of a database. It’s the key that secures all other keys
What are the database constraints in SQL?
SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
e.g. NOTNULL, DEFAULT (sets a default value), UNIQUE…
What is transaction concurrency?
In any relational database system, there is the concept of transaction. A transaction is a set of logical operations that have to be performed in a user session as a single piece of work.
The running together of two transactions, which may access the same database rows during overlapping time periods. Such simultaneous accesses, called collisions, may result in errors or inconsistencies if not handled properly. The more overlapping that is possible, the greater the concurrency.
https://www.sqlshack.com/concurrency-problems-theory-and-experimentation-in-sql-server/
