UNIT2(LABORATORY)

Question 1

insert into tbl_sample
values (‘juan’,143.44,1, 150.65);

Answer 1

iNSERTING ROWS

Question 2

//SQL Server
insert into tbl_sample
values (‘juan’,143.44,null, 150.65);

//MySQL
insert into tbl_sample (s_name, s_amt, s_unit, s_price)
values (‘juan’,143.44,null, 150.65);

Answer 2

Inserting Rows with Null Attributes

Question 3

insert into tbl_sample (s_name, s_amt)
values (‘juan’,143.44);

Answer 3

Inserting Rows with Optional Attributes

Question 4

//SQL Server and MYSQL
select s_name, s_amt from tbl_sample;
select * from tbl_sample;

Answer 4

Select statements

Question 5

//SQL Server and MYSQL
update tbl_sample
set s_amt = 255.20
where s_name = ‘juan’;

Answer 5

Update statements

Question 6

//SQL Server and MYSQL
delete from tbl_sample
where s_name = ‘juan’;

Answer 6

Delete statements

Question 7

INSERT INTO tablename SELECT columnlist FROM tablename

Answer 7

Inserting Rows from another table

Question 8

insert into tbl_food(fd_name,fd_date) select ck_name,ck_date from tbl_cake;

Answer 8

//insert all data from tbl_Cake to tbl_Food

Question 9

select fd_name,fd_date from tbl_food
where fd_name = ‘mocha’;

Answer 9

Select Queries

Question 10

//SQL Server
select ck_name,ck_date from tbl_cake
where ck_date >= ‘1/1/2001’;

//MySQL
select ck_name,ck_date from tbl_cake where ck_date > ‘1990-1-1’;

Answer 10

Comparison Operators on Dates

Question 11

//SQL Server and MySQL
select fd_name,fd_date,fd_price * fd_quantity as ‘total’ from tbl_food;

Answer 11

Computed Columns and Aliases

Question 12

/SQL Server and MySQL/
select fd_name,fd_date from tbl_food
where fd_name = ‘choco’ and fd_date = ‘1994-10-23’;

Answer 12

Logical Operators: AND

Question 13

/SQL Server and MySQL/
select fd_name,fd_date from tbl_food
where fd_name = ‘choco’ and fd_date = ‘1994-10-23’;

select  fd_name,fd_date from tbl_food  where fd_name = 'mocha' or (fd_name = 'choco' and fd_date = '1994-10-23');

Answer 13

Logical Operators: OR

Question 14

/SQL Server and MySQL/
select fd_name,fd_date from tbl_food
where not(fd_name = ‘mocha’);

Answer 14

Logical Operators: NOT

Question 15

/SQL Server and MySQL/
select * from tbl_food
where fd_price between 10 and 100;

Answer 15

The BETWEEN Special Operator

Question 16

/SQL Server and MySQL/
select * from tbl_food
where fd_name is null;

Answer 16

The IS NULL Special Operator

Question 17

the following query would find all fd_name rows that has the letter ‘o’

Answer 17

select * from tbl_food
where fd_name like ‘%o%’

Question 18

The following query would find all fd_name rows that has any letter in between ‘ch’ and ‘co’ such as ‘chaco’, ‘ chbco’, etc.

Answer 18

select * from tbl_food
where fd_name like ‘ch_co’

Question 19

The following query would find all fd_name rows that has any letter in between a, e, i, o, u such as ‘choca’, ‘choce’, ‘choci’, ‘choco’, ‘chocu’ etc.

Answer 19

/SQL Server/
select * from tbl_food
where fd_name like ‘choc[aeiou]’

/Mysql/
select * from tbl_food
where fd_name REGEXP ‘choc[aeiou]’

Question 20

The following query would find all fd_name rows that has any letter EXCEPT a, e, i, o, u in between such as ‘chocb’, ‘chocc’, ‘chocd’, ‘chocf’, ‘chocg’ etc.

Answer 20

/SQL Server/
select * from tbl_food
where fd_name like ‘choc[^aeiou]’

/Mysql/
select * from tbl_food
where fd_name REGEXP ‘choc[^aeiou]’

Question 21

/SQL Server and MySQL/
select * from tbl_food
where fd_name in (‘choco’,’mocha’);

Answer 21

The IN Special Operator

Question 22

select * from tbl_cake
where exists (select fd_name from tbl_food where fd_price > 5);

Answer 22

The EXISTS Special Operator