VIVA QUESTIONS
[Link] is sql?
Ans: Structured Query Language(SQL) is a language that is used to
create,modify and access the database.
[Link] are the different types of commands available in sql?
DDL(Data Definition Language) – create,alter,drop
DML(Data Manipulation Language) –
select,update,insert,delete
DCL(Data Control Language)-Grant, Revoke
TCL(Transaction Control Language)-Commit,Rollback
[Link] is a database?
Ans: Database is an organized collection of interrelated data that
serves many applications.
[Link] is a DBMS?
Ans: A DBMS(Database Management System) is a general purpose
software that facilitates creating,accessing and manipulating
databases. Ex:Oracle, MS Access, MySQL
5. What do you mean by degree and cardinality in DBMS?
Ans. Number of rows in a table is called cardinality. Number of
columns in a table is called degree.
[Link] are DDL and DML commands?
Ans: DDL commands are used for creating or deleting tables
DML commands are used for manipulating data i.e,.rows in a table.
[Link] between delete and truncate statements in sql.
Ans: DELETE command deletes only the rows from the table based
on condition in where [Link] does not free the space containing the
table.
TRUNCATE statement is used to delete all the rows from the table
and free the space containing the table.
[Link] represents a value that is unavailable,undefined or unknown.
9. What do you mean by data type?
Ans. Data type refers to the type of data we are entering in the column
of the table.
[Link] two String Data type in MySQL.
Ans. Char and Varchar
[Link] data type is used for “Date of birth” field in Student table.
Ans. Date
[Link] is the format of date in MySQL.
Ans. yyyy/mm/dd
[Link] is the difference between Char and Varchar.
Ans. Char is fixed length data type while Varchar is variable length
data type.
14. Which data type in MySQL is used to store logical values?
Ans. Boolean
[Link] do you mean by keyword in MySQL?
Ans. Keyword refers to a word which has special meaning in MySQL.
[Link] is the difference between drop and delete command?
Ans. Drop command delete the data as well as structure of table
permanently from database while delete command delete only the
data from the table.
17. Differentiate between Alter and Update command.
Ans. Alter command is used to change the structure of table and
Update command is used to modify the data of table.
18. What is group by clause?
Ans. This clause is used to arrange same data in groups using some
aggregate functions like Sum, average etc.
19. Define the following term
1. Primary Key
2. Foreign Key
3. Candidate Key
4. Alternate Key
Ans.
1. A field which uniquely identifies each and every record in table
is called primary key.
2. A FOREIGN KEY is a field (or collection of fields) in one table
that refers to the PRIMARY KEY in another table. It is used to
link two tables.
3. Those fields which can act as a primary key are called
candidates key.
4. Those fields which are candidate keys but not selected as
primary key.
[Link] the two wild card characters in sql used while comparing
strings with like operator.
Ans: percent(%) matches any string
Underscore(_) matches any one character
[Link] is the difference between count() and count(*)?
Ans: count() function returns the [Link]. non-NULL values in a
column.
Count(*) returns the total [Link] in a column including NULL
values.
[Link] between where and having clause.
Ans: Where clause works with respect to the whole table but having
works on group only.
Where is used to put a condition on individual row of table whereas
having is used to put a condition on a group formed by group by
clause.
[Link] is an sql join?
Ans: The process of combining data from multiple tables is called a
join.
[Link] is order by clause?
Ans: Order by clause is used to sort the records in ascending or
descending order based on one or more columns. Default is
ascending.
[Link] is having clause?
Ans: Having clause is used in select statement to filter the records
returned in group by clause based on a condition.
[Link] are aggregate functions?Name some aggregate functions in
sql.
Ans: Aggregate functions are multiple-row functions which works on
multiple rows.
Aggregate functions in sql are min(),max(),count(),sum(),avg()
[Link] the special operators in sql.
Ans: Special Operators in sql are,
BETWEEN-checks whether an attribute value is within a page
IS NULL/IS NOT NULL-checks whether an attribute value is
null or not
LIKE/NOT LIKE-checks whether an attribute matches a given
string pattern or not
IN-checks whether an attribute value matches any value with a
given list
DISTINCT – permits only unique values and eliminates
duplicate values.
[Link] are constraints? Define different constraints in sql.
Ans: Constraints are the rules enforced on data or columns on a table.
Following are the constraints used in sql,
NOT NULL Constraint – Ensures that a column cannot have
NULL value.
DEFAULT Constraint – Provides a default value for a column
when no value is specified.
UNIQUE Constraint – Ensures that all values in a column are
unique.
PRIMARY KEY Constraint – Uniquely identifies rows in a
table.
FOREIGN KEY Constraint – Uniquely identifies rows in any
other database table.
CHECK Constraint – Ensures that all values in a column satisfy
certain conditions
DISTINCT keyword is used to remove duplicate records from the
table.
DESCRIBE or DESC command is used to view the structure of table.
SELECT statement is used to fetch data from one or more databases.
Python-Sql connectivity
commit()- used to make the changes made in the database permanent
rollback()-reverts the changes made by current transaction
autocommit() – it’s value is assigned True or False to enable or
disable the auto-commit feature of mysql.
connect() statement creates a connection to the mysql server and
returns a mysql connection object.
is_connected() is the function of MySQLConnection class through
which we can verify if our python application is connected to
MySQL.
cursor() method of connection object is used to create a cursor object.
execute() method of cursor object is used to execute the sql queries
from python. It accepts arguments of sql statements in string format.
fetchone() – fetches the next row of query result set. It returns a tuple
fetchall() – fetches all the rows in the result set and returns a list of
tuples
fetchmany(size) – fetches the specified [Link] from the result set
and returns a list of tuples
rowcount – it is a read-only attribute that returns the [Link]
affected by execute() method
close()-to disconnect the database connection