SQL Flashcards
[1] What is oracle epoch function?
[2] What given code do: extract(epoch from bilet.data_zakupu) - extract(epoch from bilet.bilet_start) do?
[1] epoch is a function that returns the number of seconds that have elapsed since 1 January 1970 (in another OS date can be different)
[2] it determines the difference between when ticket was activated and when it was bought
What is the difference between UNION and UNION ALL?
UNION - removes duplicated records (where all columns in the results are the same)
UNION ALL - does not remove additional columns
What is the difference between UNION and UNION ALL?
UNION - removes duplicated records (where all columns in the results are the same)
UNION ALL - does not remove additional columns
What are join clauses?
The join clause combines columns with related values from two or more tables to create a new table. There are four main types of SQL join clause:
a) JOIN returns records with matching values in both tables
b) LEFT JOIN returns all records from the left table and matching records from the right table
c) RIGHT JOIN returns all records from the right table and matching records from the left table
d) FULL JOIN returns all records from both tables
How to create empty tables with the same structure as another table?
create table xyz_new as select * from xyz where 1=0;
LIMITATIONS:
The following things will not be copied to the new table:
sequences
triggers
indexes
some constraints may not be copied
materialized view logs
What is the difference between the RANK() and DENSE_RANK() functions?
RANK
The RANK() function in the result set defines the rank of each row within your ordered partition. If both rows have the same rank, the next number in the ranking will be the previous rank plus a number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7.
The DENSE_RANK() function assigns a distinct rank to each row within a partition based on the provided column value, with no gaps. It always indicates a ranking in order of precedence. This function will assign the same rank to the two rows if they have the same rank, with the next rank being the next consecutive number. If we have three records at rank 4, for example, the next level indicated is 5.
What is a Primary key?
Table - SQL Interview Questions - EdurekaA Primary key in SQL is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table.
Uniquely identifies a single row in the table
Null values not allowed
What are Constraints?
Constraints in SQL are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement. The sample of constraints are:
NOT NULL
CHECK
DEFAULT
UNIQUE
PRIMARY KEY
FOREIGN KEY
What is the difference between DELETE and TRUNCATE statements?
TRUNCATE:
- remove all records
- it put locks on the whole table
- faster than delete as it doesn’t scan records
- DDL query
- do not need to commit, you cannot rollback data
DELETE:
- can remove 0 to all records
- slower than TRUNCATE
- DML query
- need commit, can be rolledback
What do you mean by Denormalization?
A design techniques that helps to increase the performance of the entire infrastructure as it introduces redundancy into a table
What is Normalization and what are the advantages of it?
Normalization in SQL is the process of organizing data to avoid duplication and redundancy. Some of the advantages are:
What is the ACID property in a database?
1) Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
2) Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
3) Isolation: The main goal of isolation is concurrency control.
4) Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.
