Theory Flashcards
What is SQL?
-structured query language
-it is a database programming language used to retrieve and manage data in relational databases
-it is used to store data in the backend
what are application of SQL?
-store data
-manipulate and retrieve data
application:
banking sector
medical records
online shopping
what is a database?what is the need for a database?
it is a systematic collection of data which supports storage and manipulation of data easily
need for a database :
-managing large amount of data
-manual verification of thousand of entries in a spreadsheet is difficult
-ease of data upload (create,upload,manipulate,delete)
-data security(restrict data access)
what is a relational database?what are examples for RDs?
type of database that stores and provide access to datapoints that are related to each other.
in RD each row is called a record and column also called as fields
eg:
Mysql,oracle,microsoft sql server,sybase
what are different SQL commands?explain?
DDL-Data definition language
DQL-Data query language
DML-Data manipulation language
DCL-Data control language
TCL-Transaction control language
DDL-Data definition language
-used to define a data base
-used to create and modify the structure of the database but not
the actual data
-these type of commands are not used by the user who is
accessing the database
-eg: create,drop,alter,truncate,comment,rename
DQL-Data query language
-used to perform queries of the data
-eg:select
DML-Data manipulation language
-used to manipulate the data in the database
-eg:insert,update,delete
DCL-Data control language
-mainly deals with the right,permission and other controls of the
DB
-eg: Grant,Revoke
TCL-Transaction control language
-used to manage transactions in the database
-used to manage changes made by the DML statements.
-eg:commit,rollback
what are different SQL datatypes?
Numerics
character
Date-time
Numerics-bigint,int,tinyint,decimal,
character-fixed length,varchar,text
Date-time-date,time,year
what is key? what are different keys in DBMS explain ?
keys are an attribute or a set of attribute that are used to uniquely identify a row
super key
candidate key
primary key
alternate key
unique key
foreign key
composite key
super key-it is the set of all possible key combination.
candidate key-subset of super key with no redundant attributes
primary key-subset of candidate key.there might be multiple
candidate key in a table but only one primary key(no
null values )
alternate key-the keys other than primary key in the set of
candidate key
unique key-similar to primary key but also contain one null value
foreign key-foreign key is a field that is used to establish the link
between two tables. a foreign key in one table is used
to point the primary key in another table
composite key-keys with more than one attribute
a table can only be one primary key but it can have multiple unique keys
foreign key is used to prevent actions that would destroy the table
foreign key is a field in one table that refers to primary key in another table
table with foreign key is called child table,table with primary key is called parent table
what are constraints in sql?
used to specify rules for data in a database
-not null-ensures that a column cannot have null values
-default-assigns a default value to column when no value given
-unique
-primary key
what are different types of joints in sql?
inner join-returns records having matching values in both table
left join-returns all records from left table and matched record from right table
right join-return all record from left table and matched record from left table
full join-returns all the values from both the table and null values where the joint condition is not met
What are different SQL clauses?
Where
Order
Having
Top
Group by
where clause does not work on aggregate function therefore we have to use having clause
INNER and OUTER joints?
INNER-inner
OUTER-right,left,full
Difference b/w DBMS and RDBMS?
DBMS stores the data in the form of files
RDBMS stores the data in the form of tables
DBMS is designed to handle small amount of data
RDBMS is designed to handle large amount of data
DBMS provide support to only a single user at a time
RDBMS provides support to multiple users at a time
What is Normalization in DBMS?
it is a technique for organizing data in the database to remove data redundancy
it is the process of splitting relations in to well-structured relations that allows user to insert,delete and update tuples without introducing database inconsistencies
normalization was introduced to overcome the three anomalies
what are three types of anomalies?
if a table has redundant data it will not only use up the memory but it will make it harder to update and manage the database
three types of anomalies are
insertion
update
deletion
insertion anomaly-it is the inability to add data to the database due to absence of other data (null values not allowed)
deletion anomaly-unintended loss of data due to deletion of other data (only one department)
update anomaly-data inconsistency that results from data redundancy and partial update (say the head of the dept changes then all the employee record must be changed)
if we forget to update any one data it will lead to data inconsistency and now we can’t say which one is correct
what are three normal forms of normalization?
first normal form(1NF)
second normal form(2NF)
third normal form(3NF)
all forms are basically a set of rules that we need to follow while creating a database
first normal form
a relation will be 1NF if it contains an atomic value.
an attribute of a table cannot hold multiple values
no duplicate rows should be present
second normal form
the table must be in 1NF form
all non-key attributes are fully-functional dependent on the primary key
removes partial dependency
eg:employee_id,dept_id,office_location
third normal form
it has to be in 2NF form
all non-key attribute should be fully functionally dependent on the primary key
