SQL Flashcards
What are DML and DDL ?
Data definition language - insert, update, delete
Data manipulation language - i.e. DB schemas | how the data should redide in database | CREATE, ALTER, DROP, TRUNCATE
Aggrefate functions with examples
count, sum, min, max, avg
SELECT CITY, COUNT(CITY) FROM Customers
GROUP BY CITY
Primary key vs Unique key
Primary key - unique identifier for each row
non null
only one
clustered index
Unique - when uniquness is required
could be null
could be several
non-clustered index
What are transactions
Series of data manupulation statements that must fully complete or fully fail
What is ACID
atomic - if change is commited - then only whole change. You can never see half of change
consistent - the change can only happen if the new state of the system will be valid (none of the constraints you have on related data will ever be violated)
isolated - no one else sees any part of change until it is commited. If two transactions are executing concurrently, each one will see the world as if they were executing sequentially
durable - committed data stored forever | when transaction ended - it has been recorded (is not in RAM)
What transaction isolation levels do you know?
The degree of which one transaction is visible to another transaction
MySQL:
READ UNCOMMITTED - dirty read. could read uncomitted data
READ COMMITTED - each consistent read in same transaction sets its own snapshot
REPEATABLE READ - default. Consistent multiple reads within same transaction will get snapshot established by the first read
SERIALIZABLE.
Are db indexes useful? What is the role of them?
Speed up search queries
What kinds of joins do you know?
Inner
Left
Right
Full
What is the difference between innner join and outer join?
inner - only shows rows if they are in both tables
outer - shows rows from left/right table with empty or existing data from other table
Why many indexes are not good for performance?
makes more update/insert operations
uses more space on disk
Views. Why they are needed
avoid extra tables
allow access to views, not to tables
do not take additional store in db
Which of SELECT, UPDATE, DELETE, INSERT
performance is mostly affected by index performance
the more indexes - the slower INSERT, UPDATE, DELETE
What does it mean database de-normalization?
grouping data or adding redundant copies of data
=> improve read performance
=> decrease write performance & use more space
What are the ways to increase performance of database?
Sharding
CQRS
Replication
Indexes
De-normalization
?Isolation levels
What os the difference between SQL and NoSQL
table based RDBMS vs document based, key-value pairs, graph databases or wide-column stores
