CBSE Class 11 Information Practices Functions In My Sql Worksheet

Q1. Write the output for the following statements:

Statement

Output

Statement

Output

1

SELECT POW(2,4);

12

SELECT POW(2,-2);

2

SELECT POW(-2,3);

13

SELECT POW(-2,-3);

3

SELECT ROUND(-1.23);

14

SELECT ROUND(-1.57);

4

SELECT ROUND(-1.58);

15

SELECT ROUND(1.43);

5

SELECT ROUND(6.298,1);

16

SELECT ROUND(6.235,0);

6

SELECT ROUND(56.34,-1);

17

SELECT TRUNCATE(7.543,1);

7

SELECT TRUNCATE(4.567,0);

18

SELECT TRUNCATE(-76.56,1)

8

SELECT TRUNCATE(342,-2);

19

SELECT SQRT(25);

9

SELECT SQRT(125);

20

SELECT SIGN(-43);

10

SELECT SIGN(654);

21

SELECT SIGN(-25);

11

SELECT LENGTH(‘INDIA’);

22

SELECT LENGTH(‘4321’);

23

SELECT CONCAT(‘My’, ‘S’, ‘SQL’);

24

SELECT CONCAT(‘Class’, NULL, ‘XI’);

25

SELECT INSTR (‘INFORMATICS’, ‘FOR’);

26

SELECT INSTR (‘COMPUTERS’, ‘PER’);

27

SELECT INSTR (‘COMPUTER’, ‘pu’);

28

SELECT LOWER (‘INFORMATION’);

29

SELECT UPPER (‘Indian’);

30

SELECT LCASE (‘IT’);

32

SELECT UCASE (‘COMPUTER’);

Statement

Output

33

SELECT LEFT (‘Information’, 3);

34

SELECT LEFT(“SCIENCE”,6);

35

SELECT RIGHT (‘INFORMATICS’,4);

36

SELECT LTRIM (‘ Class ’);

37

SELECT RTRIM (‘ Class ’);

38

SELECT TRIM (‘ Class ’);

39

SELECT SUBSTRING (‘INFORMATICS’,3);

40

SELECT SUBSTRING (‘INFORMATION’, FROM 4);

41

SELECT SUBSTRING (‘INFORMATION’, 3,4);

42

SELECT SUBSTRING (‘INFORMATION’, -3);

43

SELECT SUBSTRING (‘INFORMATION’, -5,3);

44

SELECT SUBSTRING (‘INFORMATION’, FROM -4 FOR 2);

45

SELECT ASCII(‘B’);

46

SELECT CHAR(65);

47

SELECT CURDATE ( );

48

SELECT NOW ( );

49

SELECT SYSDATE ( );

50

SELECT DATE (‘2016-02-26 01:02:03’);

51

SELECT MONTH (‘2016-02-26’);

52

SELECT YEAR (‘2016-02-26’);

53

SELECT DAYNAME(‘2016-02-26’);

54

SELECT DAYOFMONTH(‘2016-02-26’);

55

SELECT DAYOFWEEK(‘2016-02-26’);

56

SELECT DAYOFYEAR(‘2016-02-26’);

Q1. Create a Table “Employee” with the following constraints

Q2. Add 10 records to the above table “Employee”.

Q3. Write the following SQL commands on the basis of above table “Employee”

a) Show all the information of the employees.

b) Display all the employee names, designation and salary.

c) Display all the employee names and their salary in descending order.

d) Display all the different departments in the company.

e) Display all the details of Female Employee.

f) Display the employee name, address and phone number from “HR” department.

g) Display all the details of employee name starting with “a”.

h) Display all the employee details (name, designation , department and gender) with salary 1000 OR to 2000 OR.

i) Display all the details of the staff that joined from year 2000.

j) Display the names of all the “GM” in the company. (GM- General Manager).

k) Display the names of all the employees from “finance” department with salary more than 1000

l) Display the names of all the Male employees from “transport” department with salary less than 1000 .

m) Display the details of the entire employee NOT from “Sales” department.

n) Display the name, address of employee without phone number. 

Q1. Create a Table “Class” with the following constraints (Rollno(2), Name Varchar(30), Stipend number(5), Subject Char(25), Avgmark decimal(6,2),Grade char(1));

Q2. Write the following SQL commands on the basis of above table “CLASS”

a) Display all the Non Medical stream students Name from the table Class.

b) List the Names that have grade “A” sorted by stipend in the ascending order.

c) Arrange the records Average marks wise .

d) List the records where grade is B or C.

e) Display the records where Name ends with N.

f) Display those records whose average mark in between 70 to 90.

g) Display the different Subjects in the table.

h) Display students Name where Avergemarks is more than 75.

i) Display all records in Descending order (Name) wise.

j) Insert a new row with the following data (06, ‘MITHUN’, 1300, ‘HUMANITIES’, ‘98’,’A’).