EndGame Flashcards
What is a UNIQUE constraint?
The UNIQUE constraint ensures that all values in a column are different. You can have more than one unique constraint per table.
What is a View? Materialized View?
A view is a virtual table based on the result set from a SQL statement. It is never stored and only displayed. A materialized view is a table that is stored on the disk. This allows for faster processing compared to a view because the results are already precomputed. Views are slower because they are called every time, they are used so you are always getting an updated view.
How do you create a View?
Create View view_name AS Select column FROM table Where column
What are primary and foreign keys?
Primary keys are keys that are used to uniquely identify a record in a table. Each table can only have one primary key. Primary keys are unique and not null. Foreign keys are keys used to establish relations between two tables. Foreign keys in one table refer to primary keys in another table.
What is the order of operations in a SQL statement?
From, Where, Group By, Having, Select, Order By, Limit
What are the sub languages of SQL?
DDL - Data definition language (create, alter, drop)
DML - Data manipulation language (insert, update, delete)
DCL- Data control language
DQL - Data query language (select)
TCL - Transaction control language
SCL - Session control language
What is cardinality?
Cardinality refers to the uniqueness of values in a column. When a column has high cardinality, it means that the column is very unique and there are no duplicates. When there is low cardinality, it means that there are a lot of duplicates in the column.
What is a candidate key?
A candidate key is a key that uniquely identifies a record in a table. It is similar to a primary key with the only difference being that you can have more than one candidate key. A candidate key is created by using the UNIQUE constraint.
What is a composite key?
A composite key is made of two or more columns in table that can be used to uniquely identify each row in a table. To declare a composite key, you would do Primary Key (col 1, col2) in your table creation.
What are the different constraints on a column?
Not Null, Unique, Primary Key, Foreign Key, Create Index, Default, Check
What is an ERD?
ERD stands for entity relation diagram, and it is a diagram that shows the relationships between entities. Entities are represented as tables in a database and the ERD shows relations based on certain attributes (data) of these entities.
What is the difference between the Where clause and the Having clause?
Where clause is used to filter records before a grouping is made while the Having clause is used to filters values out of group. Where clause can be said to be a pre filter and the Having clause as post filter.
Also where clause cannot be used with aggregate but the Having clause can.
What is the difference between Group By and Order By?
Group by is used to group rows that have the same values while the order by clause is used to order a column in ascending or descending order.
What is the LIKE clause?
The like clause is used for pattern matching. It is a logical operator that checks whether a string matches a particular pattern.
What is a Sub Query?
A subquery is a query that appears inside another query statement. Sub queries are often used the the select and where clause.
What is the difference between aggregate and scalar functions?
Aggregate functions are functions that operate on a collection of values and return a single value while scalar functions return a single value based on input. Some examples of aggregate functions are Avg(), Sum(), Count(), Min(), Max(). Some examples of scalar functions are Ucase(), Lcase() and round().
Explain the different types of joins?
Inner Join - joins rows from two tables based on a matching column and returns only the matches
Left Join - joins rows from two tables based on a matching column and also returns rows from the left table.
Right Join - same thing as left join but instead returns rows from the right table.
Cross Join - The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table.
Explain the different types of set operations?
Union - Combines result set of two or more select statements but does not include duplicates.
Union All - Same thing as Union but includes the duplicates
Intersect - Returns only the common results of the select statements.
Minus - The SQL MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT
Intersect and minus are not supported in mysql so for intersect you can use inner join and for minus you can use left join
What is the Merge statement in SQL? How would you use it?
The MERGE statement in SQL is a very popular clause that can handle inserts, updates, and deletes all in a single transaction without having to write separate logic for each of these.
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
–When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate
THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate
–When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
–When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE
THEN DELETE
What is referential integrity?
It is a database concept that is used to build and maintain logical relationships between tables to avoid logical corruption of data. Usually, referential integrity is made up of the combination of a primary key and a foreign key.
What is the difference between Truncate, Drop, and Delete?
Delete - It is a command that is used to delete records from a table. It is not meant to delete a table from a database. You can either delete all records (Delete FRom tablename) or you can delete a group of records (Delete from tablename where col = something)
Truncate - Similar to delete in that it deletes all records from a table but does not use the where clause. It is faster than delete because it doesn’t need to scan every record.
Drop - drop is used to delete the entire table from the database. The table structure is removed along with all of the data in the table.
How do you find duplicates in a table?
To find duplicates in a table you can use the group by clause in conjunction with the count aggregation function and having clause to determine if the count of particular column is more than 1.
How would you find the nth highest salary in a table?
solution 1: dense_rank() over(order by col) from table where somthing
solution 2 select from order by column limit n-1,1
What is main difference between SQL and MySQL?
SQL is a language for querying databases and MySQL is an open source database product. SQL is used for accessing, updating and maintaining data in a database and MySQL is an RDBMS that allows users to keep the data that exists in a database organized.
What are ranking functions?
dense_rank():
This function will assign rank to each row within a partition without gaps. Basically, the ranks are assigned in a consecutive manner i.e if there is a tie between values then they will be assigned the same rank, and next rank value will be one greater then the previous rank assigned.
rank():
This function will assign rank to each row within a partition with gaps. Here, ranks are assigned in a non-consecutive manner i.e if there is a tie between values then they will be assigned same rank, and next rank value will be previous rank + no of peers(duplicates).
percent_rank():
