Notes

Q1:
*********************************************************************************************************************************************************
MariaDB [0187cs171071]> create table employ(emp_id varchar(10) primary key,emp_name char(20) NOT NULL, emp_dep char(2) check(emp_dep in ('CS','IT','ME','EC')),emp_salary int(10));
Query OK, 0 rows affected (0.14 sec)

MariaDB [0187cs171071]> insert into employ values('1','Mahesh','CS',20000);
Query OK, 1 row affected (14.10 sec)

MariaDB [0187cs171071]> insert into employ values('2','Suresh','IT',1000);
Query OK, 1 row affected (1.73 sec)

MariaDB [0187cs171071]> insert into employ values('3','akash','CS',1500);
Query OK, 1 row affected (4.79 sec)

MariaDB [0187cs171071]> insert into employ values('4','jaesh','CS',1500);
Query OK, 1 row affected (10.29 sec)

MariaDB [0187cs171071]> show tables;
+------------------------+
| Tables_in_0187cs171071 |
+------------------------+
| customer |
| employ |
| show_time |
| tbl_category |
| tbl_movie |
| tbl_product |
| tbl_screen |
| tbl_seat |
| tbl_theater |
| tbl_user |
+------------------------+
10 rows in set (0.00 sec)

MariaDB [0187cs171071]> desc employ;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_id | varchar(10) | NO | PRI | NULL | |
| emp_name | char(20) | NO | | NULL | |
| emp_dep | char(2) | YES | | NULL | |
| emp_salary | int(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [0187cs171071]> select * from employ;
+--------+----------+---------+------------+
| emp_id | emp_name | emp_dep | emp_salary |
+--------+----------+---------+------------+
| 1 | Mahesh | CS | 20000 |
| 2 | Suresh | IT | 1000 |
| 3 | akash | CS | 1500 |
| 4 | jaesh | CS | 1500 |
+--------+----------+---------+------------+
4 rows in set (0.54 sec)

Q1.1:MariaDB [0187cs171071]> select emp_name from employ where emp_dep='CS';
+----------+
| emp_name |
+----------+
| Mahesh |
| akash |
| jaesh |
+----------+
3 rows in set (1.31 sec)

2:.select emp_name form employ where emp_name like 'a%' or emp_name like 'j%';

| 3 | akash | CS | 1500 |
| 4 | jaesh | CS | 1500 |
+--------+----------+---------+------------+

5::.select count('emp_dep') from employ;
+------------------+
| count('emp_dep') |
+------------------+
| 4 |
+------------------+
1 row in set (0.54 sec)

3::select avg(emp_salary) from employ group by emp_dep;
+-----------------+
| avg(emp_salary) |
+-----------------+
| 7666.6667 |
| 1000.0000 |
+-----------------+
2 rows in set (0.01 sec)

3::select avg(emp_salary) from employ where emp_salary<20000 group by emp_dep;
+-----------------+
| avg(emp_salary) |
+-----------------+
| 1500.0000 |
| 1000.0000 |
+-----------------+
2 rows in set (0.34 sec)

4:: select * from employ order by emp_salary Desc limit 1 offset 2;
+-----------------+
| 3 | akash | CS | 1500 |
+-----------------+

1 rows in set (0.27 sec)

**********************************************************************************************************************************
Q2:

create table student(roll_num int(12) primary key,name char(20) NOT NULL, branch char(2) check in ('CS','IT','ME'));

create table result(roll number int(12), sem int(2),per int(6));

#natural join
select * from student natural join result;

select * from student left outer join result;

select * from student right outer join result;

select * from student full outer join result;