DATABASE QUERY

1. What is meant by a primary key ?

Ans. A primary key is a field in the table/file that uniquely identifies every record in a file.

2. Define the following terms :

(a) relation
(b) primary key
(c) cardinality
(d) tuple
(e) candidate key
(f) degree
(g) attribute
(h) cartesian product
(i) domain

Ans.
(a) Relation. A relation is a table having atomic values, unique rows and unordered rows and columns.

(b) Tuple. A row in a relation is known as tuple.

(c) Attribute. A column of a table is known as an attribute.

(d) Domain. A domain is a pool of values from which the actual values appearing in a given column are drawn.

(d) Primary key. A primary key is a set of one or more attributes that can uniquely identify tuples within the relation.

(e) Candidate key. All attribute combinations inside a relation that can serve as primary key are candidate keys as they are candidates for the primary key position.

(f) Cartesian product. The cartesian product of two relations A and B written as Ax B results into a new relation with all possible combinations of the tuples of the two relations operated upon. All tuples of first relation are concatenated with all the tuples of second relation to form the tuples of the new relation.

(g) Cardinality. The cardinality of a relation means the number of tuples (rows) in the relation.

(h) Degree. The degree of a relation means the number of attributes (columns) in the relation.

3. Differentiate between DDL and DML.

Ans. The DDL provides statements for the creation and deletion of tables and indexes. The DML provides statements to enter, update, delete data and perform complex
queries on these tables.

4. What is composite primary key ?

Ans. The primary key which is created on multiple columns in a table is generally considered as the Composite primary key.

5. What is the difference between primary key and unique constraints ?

Ans. Primary key cannot have NULL value, the unique constraints can have NULI, values. There is only one primary key in a table, but there can be multiple unique constrains. The primary key creates the cluster index automatically but the Unique key does not.

6. What is a join in SQL? What are the types of joins ?

Ans. An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them.

7. Name some common DDL commands.

Ans. Create Table, Create Index, Alter Table, Drop table.

8. Name some common DML commands.

Ans. Select, Insert Into, Update, Delete

9. Can you use = comparison operator to compare Null values in a select query ?

Ans. No, the operator to compare Null values is ‘is’ operator.

10. In a query, is it possible for the users to avoid the duplicate records? How this can be done?

Ans. Yes, it is very much possible. For instance, issuing command SQL SELECT DISTINCT will return the unique values.

11. What is the significance of GROUP BY clause in a SQL query ?

Ans. The GROUP BY clause combines all those records that have identical values in a particular field or a group of fields. This grouping results into one summary record per group if group-functions are used with it.

12. What is the difference between a WHERE clause and a HAVING clause of SQL SELECT statement ?

Ans. The difference between WHERE and HAVING clause is that WHERE conditions are applicable on individual rows whereas HAVING conditions are applicable on groups as formed by GROUP BY clause.

13. What is the significance of the default constraint in SQL?

Ans. It is used when it comes to including a default value in a column in case there is no new value provided at the time a record is inserted.

14. What is the difference between Delete and Truncate commands ?

Ans. The Delete command can delete one or more rows of a relation depending upon the given condition. The Truncate command deletes all the records from a relation.

15. What is the difference between DROP TABLE and Truncate commands ?

Ans. The Truncate command deletes all the rows from a table but the table structure stays, i.e., an empty table still lies in the database.

The drop table command removes the table object from the database. After drop
table, no table with the given name exists in the database.

17 . What is an Index?

Ans. An Index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

18. What is a constraint ?

Ans. Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement.
Commonly used SQL constraints are :

NOT NULL
CHECK
DEFAULT
UNIQUE
PRIMARY KEY
FOREIGN KEY

19. What is difference between candidate key and primary key ?

Ans. Any attribute which uniquely identifies a row in a table is candidate key for the table. We select one of the candidate keys as Primary key. All candidate keys which are not chosen as primary key are Alternate keys. The key which uniquely identifies the rows of the table and which is made up of more than one attribute is called Composite key. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key one of them will become the primary key and the rest are called alternate keys.

20. How do you reformat system date in MySQL ? Explain various formats.

Ans. One can change the date time format with DATE_FORMAI() function.
For example,
select date_format (datetime column ‘Y-M-dH: i: 5’ ) from table_name ;

21. What is the command for counting all the rows in a table in MySQL ?

Ans. Select (count*) from table_name

22. What’s MySQL?

Ans. MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility.

23. What is DDL, DML and DCL ?

Ans. If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database

24. If the value in the column is repeatable, how do you find out the unique values ?

Ans. Use DISTINCT in the query, such as SELECT DISTINCT user _firstname FROM users; We can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user firstname) FROM users ;

25. How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
Ans.

SELECT team_name FROM teams
WHERE team_won IN (2, 4, 6, 8)

27. What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps
USING (user_id) ?

Ans. It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT
JOIN isps WHERE users.user_id=isps.user_id

28. On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do ?

Ans. What it means is that some of the data that we are trying to delete is still alive in another table. Like if we have a table for universities and a table for students, which

29. How do you concatenate strings in MySQL ?

Ans. CONCAT (string1, string2, string3)

30. How do you get a portion of a string ?

Ans. SELECT SUBSTR(title, 1, 10) FROM questions;

31. What’s the difference between CHAR_LENGTH and LENGTH ?

Ans. The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

32. What do % and _ mean inside LIKE statement ?

Ans. % corresponds to 0 or more characters; – is exactly one character.

33. How do you get the month from a timestamp ?

Ans. SELECT MONTH(daytimestamp) from stored Time;

33. How do you add three minutes to a date ?

Ans. ADDDATE(publication _date, INTERVAL 3 MINUTE)

34. Explain advantages of InnoDB over MyISAM.

Ans. Row-level locking, transactions, foreign key constraints and crash recovery.

35. Explain the difference between FLOAT, DOUBLE and REAL.

Ans. FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now. If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this Ans. 999.99 to -99.99. Note that with the negative number the minus sign is
considered one of the digits.

36. What happens if a table has one column defined as TIMESTAMP ?

Ans. That field gets the current timestamp whenever the row gets altered.

37. But what if you really want to store the timestamp data, such as the publication date of the article ?

Ans. Create two columns of type TIMESTAMP and use the second one for the real
data.

38 . If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table ?

Ans. CHAR(3), since MySQL automatically adjusted the data type.

39 . What is database connectivity?

Ans. Database connectivity refers to connection and communication between an application and a database system.

40. What is Connection ? What is its role ?

Ans. A Connection (represented through a connection object) is the session between the application program and the database. To do anything with database, one must have a connection object.

41. What is a result set ?

Ans. A result set refers to a logical set of records that are fetched from the database by executing a query and made available to the application-program.

42. Which package must be imported in Python to create a database connectivity application?

Ans. There are multiple packages available through which database connectivity applications can be created in Python. One such package is mysql.connector.