1. What is a primary key?
Ans. A primary key is a field or a combination of fields that can
uniquely identify a row/tuple in a table/relation.
2. What is a unique key ? Is it a primary key?
Ans. A key in a table/relation is any non-primary-key field which also
stores unique values for each row just like a primary-key does. But only one
key is designated as a primary key. So unique refers to a unique non-key field
of a table.
3. How many primary keys and unique keys can be there in a table?
Ans. There can be multiple unique keys in a table but there can be only
primary key in a table.
4. What is a foreign key?
Ans. A foreign key is a field of a table which is the primary key of
another table Through a foreign key a relationship is established between two
tables.
5. What is a composite primary key?
Ans. If a table/relation has a primary key which is a combination of
multiple columns of a table, it is known as the composite primary key.
6. What is a tuple?
Ans. A tuple refers to a row of a relation.
7. What is SQL?
Ans. SQL refers to Structured Query Language, a standard query language
used by RDBMSs to create, maintain and query upon their databases.
8. Are SQL and MySQL the same? How?
Ans. SQL is a standard query languages used by RDBMSs whereas MySQL is
an example of RDBMS software. MySQL RDBMS uses SQL language to query its upon
its databases.
9. What is DDL?
Ans. DDL refers to the Data Definition Language component of SQL. The
commands are used to create various components of a database such as tables,
view indexes, triggers etc.
10. Give some examples of DDL commands.
Ans. CREATE, ALTER, DROP
11. What is DML?
Ans. DML refers to the Data manipulation Language component of SQL. The
DM commands are used to manipulate and query upon the data stored in various
tables a database.
12. Give some examples of DML commands.
Or
Write the names of any two DML commands of SQL
Ans. INSERT, UPDATE, SELECT
13. In SQL, write the query to display the list of tables stored in
a database.
Ans. SHOW TABLES
14. What are constraints?
Ans. Constraints are the rules or conditions imposed on various
attributes of a table in a database so that only the data that satisfy these
rules and conditions can get stored I the data table.
15. Give some examples of integrity constraints.
Ans.
NOT NULL
PRIMARY KEY
UNIQUE
CHECK
16. What is the role of NOT NULL constraint?
Ans. It indicates that in the data being inserted, the column must have
some value and cannot be left NULL.
17. What is the role of UNIQUE constraint?
Ans. This constraint ensures that for an attribute there will be a
unique value for each row and no value is being repeated in any other row for
that attribute.
18. What is the role of PRIMARY KEY constraint?
Ans. This constraint implements NOT NULL and UNIQUE constraints together
and Designated an attribute or a set of attributes as the primary key of the
table so that it Value can identify each record with a unique identity.
19. What is the role of FOREIGN KEY constraint?
Ans. This constraint is used to ensure the referential integrity of data
in the table. It matches the value of the column designated as the foreign key
in one table with another table’s PRIMARY KEY.
20. What is the role of CHECK constraint?
Ans. This constraint ensures whether the value in columns fulfils the
specified condition.
21. Which subdivision of SQL is used to put values in tables and
which one to create tables?
Ans. Data Manipulation Language (DML) is used to put values in tables
and Data Definition Language (DDL) is used to create tables.
22. Are NULL values are same as a zero or a blank space?
Ans. No, A NULL value isn't the same as a zero or a blank space. A zero
is a legal numeric value and a blank space is a legal character value, whereas
NULL is legal empty value that cannot be accessed or compared with other
values.
23. What will the SELECT DISTINCT command do?
Ans. The SELECT DISTINCT command will fetch only unique rows from a
table as per the defined criteria.
24. What will the SELECT ALL command do?
Ans. The SELECT ALL command will fetch all the rows from a table as per
the defined criteria.
25. What is the purpose of WHERE clause?
Ans. The WHERE clause is used to specify the filtering condition for
various SQL commands.
26. What is the operator used to compare patterns in SELECT command
?
Ans. LIKE operator.
27. What is the operator used to compare values against a range in
SELECT command?
Ans. BETWEEN operator.
29. What is the operator used to compare values against a set of
values in SELECT command?
Ans. IN operator.
30. In SQL, what is the use of IS NULL operator?
Ans. To check if the column has null value/no value.
31. What is wrong with the following statement?
SELECT FROM Employee WHERE
grade = NULL;
Write the corrected
form of the above SQL statement.
Ans. A relational operation is not used with the NULL clause.
The corrected form is
SELECT FROM Employee WHERE
grade is NULL;
32. Which SQL aggregate function is used to count all records of a
table?
Ans. COUNT(*)
NOTE: In the forthcoming questions,
we have also shown the MySQL prompt i.e., before the query. The prompt is not a
part of the query. Some of the queries outputs also have been shown below the
queries.
33. Write a query to create a string from the ASCII values 70, 65,
67, 69.
Ans. mysql> SELECT CHAR (70, 65, 67, 69);
Outputà FACE
33. Write a query to create a string from the ASCII values 65, 67.3,
'68.3"
Ans. mysql> SELECT CHAR (65, 67.3, ‘69.3');
Outputà ACE
34. Write a query to concatenate the two strings: "Hello"
and "World"
Ans. mysql> SELECT CONCAT('Hello', 'World');
Outputà HelloWorld
35. Write a query to concatenate the three strings :"I",
"am" and "Strong"
Ans. mysql> SELECT CONCAT(‘I’, ‘am’, 'Strong');
Outputà IamStrong
36. Display names 'MR. B. OBAMA' and 'MS. I. Gandhi' into lowercase.
Ans: mysql> SELECT LOWER (‘MR. B. OBAMA’) LOWER (‘MS. I.
Gandhi');
Outputà mr. b. obama | ms. 1. gandhi
37. Display 4 characters extracted from 3rd left character onwards from the string "ABCDEFG"
Ans. mysql> SELECT SUBSTR(ABCDEFG, 3, 4) "Subs";
Outputà CDEF
38. Display 4 characters extracted from 5th right character onwards
from the string 'ABCDEFG'
Ans. mysql> SELECT SUBSTR(ABCDEFG", -5, 4) "Subs";
Outputà CDEF
39. Convert and display string 'Large' into uppercase.
Ans. mysql> SELECT UPPER(‘Large’) "Uppercase";
Outputà LARGE
39. Write a query to remove leading spaces of the string.
Ans. mysql> SELECT LTRIM( ‘
RDBMS MySQL’);
Outputà RDBMS MySQL
40. Write a query to remove leading and trailing spaces from the
string ‘ Bar One
‘
Ans. mysql> SELECT TRIM (‘ Bar
One ‘);
Outputà Bar
One
41. Write query to remove leading spaces from the string ‘ Bar One
‘. Make use of TRIM() function only.
Ans. mysql> SELECT TRIM(leading ‘
‘ FROM ‘ Bar One
');
42. Write a query to remove leading characters from the string
'xxxxBar One xxxx
Ans. mysql> SELECT TRIM(LEADING 'x' FROM 'xxxxBar One xxxxxx');
Outputà Bar One xxxxxxx
43. Write a query to remove both leading and trailing x characters
from the string xxxxBar Onexxxxx’.
Ans: mysql> SELECT TRIM(BOTH 'x' FROM 'xxxxBar One xxxxxx");
Outputà Bar One
44. Write a query to remove trailing x characters from the string
'xxxxBar One xxxxx'
Ans. mysql> SELECT TRIM(TRAILING 'x' FROM 'xxxxBar One xxxxx');
Outputà xxxxxBar One
48. Display the position of occurrence of string 'OR' in the string
'CORPORATE FLOOR’.
Ans. mysql> SELECT INSTR('CORPORATE FLOOR, 'OR');
Outputà 2
49. How many characters are there in the string 'CANDIDE'?
Ans: mysql> SELECT LENGTH('CANDIDE');
Outputà 7
50. Write a query to extract institute code from a string
'USS/23/67/09'. The first three characters tell the institute code.
Ans. mysql> SELECT LEFT('USS/23/67/09, 3);
Outputà USS
51. Write a query to extract 2 digit year from a string
'USS/23/67/09. The last two characters tell the year.
Ans. mysql> SELECT RIGHT('USS/23/67/09, 2);
52. Write a query to extract a sub string from the string
'Quadratically’ which should be 6 characters long and start from 5th character of the given string.
Ans. mysql> SELECT SUBSTRING("Quadratically”,5,6);
Or
mysql> SELECT MID(“Quadratically”,5,6);
Outputà ratica
53. Write a query to find out the remainder of 11 divided by 4.
Ans. mysql> SELECT MOD (11,4);
Outputà 3
54. Write a query to display the result of 32.
Ans. mysql> SELECT POWER(3,2);
Outputà 9
55. Write a query to round off value 15. 193 to one decimal place.
Ans. mysql> SELECT ROUND (15.193, 1);
Outputà 15.2
56. Write a query to round off value 15.193 to nearest ten's.
Ans mysql> SELECT UND(15.193, -1);
Outputà 20
57. Write a query to find out the sign of value -15.
Ans. mysql> SELECT SIGN(-15);
Outputà -1
58. Write a query to find out the sign of value 15.
Ans. mysql> SELECT SIGN(15);
Outputà 1
59. Write a query to find out the square root of value 26.
Ans mysql> SELECT SQRT(26);
Outputà 5.09901951
60. Write a query to truncate value 15.79 to 1 decimal place.
Ans. mysql> SELECT TRUNCATE (15.79, 1);
Outputà 15.7
61. Write a query to truncate value 15.79 to ten's place.
Ans: mysql> SELECT TRUNCATE (15.79, -1);
Outputà 10
62. Write a query to display current date on your system.
Ans. mysql> SELECT CURDATE();
63. Write a query to extract date from a given datetime value “2023-01-31
01:02:03"
Ans: mysql> SELECT DATE('2023-01-31
01:02:03');
Outputà 2023-01-31
64. Write a query to extract month part from date 3rd Feb 2023.
Ans. mysql> SELECT MONTH(2023-02-03);
Outputà 2
65. Write a query to display the name of the month for date
01-05-2023.
Ans: mysql> SELECT MONTHNAME(2023-05-01");
Outputà May
66. Write a query to display day from the date 13-Apr-2023.
Ans. mysql> SELECT DAY('2023-04-13’);
Outputà 13
67. Write a query to extract year part from the date 3rd Feb 2023.
Ans. mysql> SELECT YEAR (‘2023-02-03’);
2023
68. Write a query to display name of weekday for date 7th
Jan 2023.
Ans: mysql> SELECT DAYNAME(‘2023-01-07');
Saturday
69. Write a query to display day of the month for date 3rd Feb 2023.
Ans. mysql> SELECT DAYOFMONTH(‘2023-02-03');
3
70. Write a query to display day of the week for the date 7th
Jan 2023.
Ans: mysql> SELECT DAYOFWEEK(‘2023-01-07');
7
71. Write a query to display day of the year for the date 13th
Feb 2023.
Ans. mysql> SELECT DAYOFYEAR (‘2023-02-13’);
44
72. Write a query to display current date and time.
Or
Write the SQL command that will display the current time and date.
Ans. mysql> SELECT NOW();
2023-01-02 15:27:20
74. Predict the output of the following:
(i) SELECT ROUND(26.365675, 2);
(ii) SELECT DAYOFMONTH('2020-88-25');
(ii) SELECT TRUNCATE (11.28436, 1);
(iv) SELECT MID('Pandemic Year', 2,3);
(v) SELECT INSTR('CODE UNICODE', 'CO');
(vi) SELECT INSTR('DCODE UNICODE', 'CO');
(vii) SELECT LEFT('Informatics',3), RIGHT('Practices',3);
(viii) SELECT LENGTH (CONCAT('INTER', 'NETWORK', 'KING'));
(ix) SELECT ROUND (234.7894, -1)*5;
(x) SELECT ROUND(234.7894, 0)*5;
(xi) SELECT ROUND(234.7894, -2)*5;
(xii) SELECT MID('One Good',3,4);
(xiii) SELECT TRUNCATE (17.4975,2);
(xiv) SELECT RIGHT (CONCAT('PYTHON', 'PRACTICES', ‘INFORMATICS’),9);
(xv) SELECT MID('OnlineEducation',2,4);
(xvi) SELECT ROUND(64.26446,3);
(xvii) SELECT INSTR('INFORMATICS PRACTICES', 'ACT');
(xviii) SELECT POW(INSTR('Success@hardwork', '@'),2);
(xix) SELECT POW (LENGTH("WORK'), 3);
(xx) SELECT MONTH(2820-11-23)*POW(2,3);
Ans.
|
(i) 26.37 |
(ii) 11.2 |
(iii) 25 |
(iv) and |
(v) 1 |
(vi) 2 |
(vii) Inf ces |
(viii) 16 |
(ix) 1150 |
(x) 1175 |
|
(xi) 1000 |
(xii) e Go |
(xiii) 17.49 |
(xiv) FORMATICS |
(xv) nlin |
(xvi) 64.264 |
(xvii) 15 |
(xviii) 64 |
(xix) 64 |
(xx) 88 |
No comments:
Post a Comment