# CS RECORD PRACTICALS
**1. To create a table EMPLOYEE with the following attributes: ENO, ENAME,GENDER,DEPTNO, JOB, DOJ (Date of Joining), and SALARY**
### Query:
```sql=
create table employee
(ENO int, ENAME varchar(20), GENDER varchar(8),
DEPTNO int, JOB varchar(20), DOJ date, SALARY int);
```
**2. To add PRIMARY Key constraint to ENO**
### Query:
```sql=
alter table employee add primary key (ENO);
```
**3. Write SQL statement to add 5 records into the relation EMPLOYEE**
### Query:
```sql=
insert into EMPLOYEE
(ENO, ENAME, GENDER, DEPTNO, JOB, DOJ, SALARY)
VALUES
(1, "Jane Clark", "F", 30, "CFO", "2020-04-09",35500),
(2, "Jack Smith", "M", 30,"COO", "2018-04-09",35000),
(3, "Mark","F",50,"VP","2000-03-04",40000),
(4, "Antony Dolokov","M",60,"PR","2004-08-03",38000),
(5, "Leo Tolstoy","M",70,"CEO","1999-09-02",100000);
```
### Output:
| "ENO" | "EName" | "GENDER" | "DEPTNO" | "JOB" | "DOJ" | "SALARY" |
|-------|------------------|----------|----------|-------|--------------|----------|
| "1" | "Jane Clark" | "F" | "30" | "CFO" | "2020-04-09" | "35500" |
| "2" | "Jack Smith" | "M" | "30" | "COO" | "2018-04-09" | "50000" |
| "3" | "Mark" | "F" | "50" | "VP" | "2000-03-04" | "40000" |
| "4" | "Antony Dolokov" | "M" | "60" | "PR" | "2004-08-03" | "38000" |
| "5" | "Leo Tolstoy" | "M" | "70" | "CEO" | "1999-09-02" | "100000" |
**4. To add 2 tuples by specifying the a"ributes of the rela!on EMPLOYEE:**
### Query:
```sql=
insert into employee values
(6,"Vladmir","M",10,"Window Cleaner","2000-03-04",25000);
insert into employee values
(7,"Donald Trump","M",20,"Janitor","2003-03-03",20000);
```
### Output:
| "ENO" | "EName" | "GENDER" | "DEPTNO" | "JOB" | "DOJ" | "SALARY" |
|-------|------------------|----------|----------|------------------|--------------|----------|
| "1" | "Jane Clark" | "F" | "30" | "CFO" | "2020-04-09" | "35500" |
| "2" | "Jack Smith" | "M" | "30" | "COO" | "2018-04-09" | "50000" |
| "3" | "Mark" | "F" | "50" | "VP" | "2000-03-04" | "40000" |
| "4" | "Antony Dolokov" | "M" | "60" | "PR" | "2004-08-03" | "38000" |
| "5" | "Leo Tolstoy" | "M" | "70" | "CEO" | "1999-09-02" | "100000" |
| "6" | "Vladmir" | "M" | "10" | "Window Cleaner" | "2000-03-04" | "25000" |
| "7" | "Donald Trump" | "M" | "20" | "Janitor" | "2003-03-03" | "20000" |
**5. To display all attributes of relation EMPLOYEE for the DEPTNO 10**
### Query:
```sql=
select * from employee where DEPTNO=10;
```
### Output:
| "ENO" | "EName" | "GENDER" | "DEPTNO" | "JOB" | "DOJ" | "SALARY" |
|-------|-----------|----------|----------|------------------|--------------|----------|
| "6" | "Vladmir" | "M" | "10" | "Window Cleaner" | "2000-03-04" | "25000" |
**6. To display ENO, ENAME and SALARY for all the tuples of the relation EMPLOYEE**
### Query:
```sql=
Select ENO, ENAME, SALARY from employee;
```
### Output:
| "ENO" | "ENAME" | "SALARY" |
|-------|------------------|----------|
| "1" | "Jane Clark" | "35500" |
| "2" | "Jack Smith" | "50000" |
| "3" | "Mark" | "40000" |
| "4" | "Antony Dolokov" | "38000" |
| "5" | "Leo Tolstoy" | "100000" |
| "6" | "Vladmir" | "25000" |
| "7" | "Donald Trump" | "20000" |
**7. To display ENO and ENAME, GENDER in alphabe!cal order of ENAME**
### Query:
```sql=
Select ENO, ENAME, GENDER from employee order by ENAME;
```
### Output:
| "ENO" | "ENAME" | "GENDER" |
|-------|------------------|----------|
| "4" | "Antony Dolokov" | "M" |
| "7" | "Donald Trump" | "M" |
| "2" | "Jack Smith" | "M" |
| "1" | "Jane Clark" | "F" |
| "5" | "Leo Tolstoy" | "M" |
| "3" | "Mark" | "F" |
| "6" | "Vladmir" | "M" |
**8. To display the maximum, minimum and average salary from the relation EMPLOYEE**
### Query:
```sql=
select avg(salary),min(salary),max(salary) from employee;
```
### Output:
| "avg(salary)" | "min(salary)" | "max(salary)" |
|---------------|---------------|---------------|
| "44071.4286" | "20000" | "100000" |
**9. To display details of all the Employees whose salary is between 45,000 and Rs.70,000, sorted in descending order of SALARY**
### Query:
```sql=
select * from employee where salary > 45000
and salary < 70000;
```
### Output:
| "ENO" | "EName" | "GENDER" | "DEPTNO" | "JOB" | "DOJ" | "SALARY" |
|-------|--------------|----------|----------|-------|--------------|----------|
| "2" | "Jack Smith" | "M" | "30" | "COO" | "2018-04-09" | "50000" |
**10. To display dissimilar DEPTNO in ascending order**
### Query:
```sql=
select distinct(DEPTNO) from employee
```
### Output:
| "DEPTNO" |
|----------|
| "30" |
| "50" |
| "60" |
| "70" |
| "10" |
| "20" |
**11. To increase the SALARY by Rs.3,500 for those working in DEPTNO 30**
### Query:
```sql=
update employee set salary = salary + 3500
where deptno = 30;
select * from EMPLOYEE where deptno = 30;
```
### Output:
| "ENO" | "EName" | "GENDER" | "DEPTNO" | "JOB" | "DOJ" | "SALARY" |
|-------|--------------|----------|----------|-------|--------------|----------|
| "1" | "Jane Clark" | "F" | "30" | "CFO" | "2020-04-09" | "42500" |
| "2" | "Jack Smith" | "M" | "30" | "COO" | "2018-04-09" | "57000" |
**12. To increase the size of ENAME attribute by 5 bytes**
### Query:
```sql=
alter table employee modify ENAME varchar(25);
```
**13. To add a new attribute CONTACT to hold a 10-digit mobile number (Use char type**
### Query:
```sql=
alter table employee add column CONTACT char(10);
```
### Output:
| "ENO" | "EName" | "GENDER" | "DEPTNO" | "JOB" | "DOJ" | "SALARY" | "CONTACT" |
|-------|------------------|----------|----------|------------------|--------------|----------|-----------|
| "1" | "Jane Clark" | "F" | "30" | "CFO" | "2020-04-09" | "42500" | "null" |
| "2" | "Jack Smith" | "M" | "30" | "COO" | "2018-04-09" | "57000" | "null" |
| "3" | "Mark" | "F" | "50" | "VP" | "2000-03-04" | "40000" | "null" |
| "4" | "Antony Dolokov" | "M" | "60" | "PR" | "2004-08-03" | "38000" | "null" |
| "5" | "Leo Tolstoy" | "M" | "70" | "CEO" | "1999-09-02" | "100000" | "null" |
| "6" | "Vladmir" | "M" | "10" | "Window Cleaner" | "2000-03-04" | "25000" | "null" |
| "7" | "Donald Trump" | "M" | "20" | "Janitor" | "2003-03-03" | "20000" | "null" |
**14. To add NOT NULL constraint to EName attribute**
### Query:
```sql=
alter table employee modify EName varchar(25) not null;
```
**15. To display the DEPTNO and number of employees working in each DEPTNO**
### Query:
```sql=
select DEPTNO, count(DEPTNO)
from EMPLOYEE group by DEPTNO having count(DEPTNO) > 0;
```
### Output:
| "DEPTNO" | "count(DEPTNO)" |
|----------|-----------------|
| "30" | "2" |
| "50" | "1" |
| "60" | "1" |
| "70" | "1" |
| "10" | "1" |
| "20" | "1" |
**16. To display the DEPTNO and number of employees working in each DEPTNO, that has at least two employees**
### Query:
```sql=
select DEPTNO, COUNT(DEPTNO)
from EMPLOYEE group by DEPTNO having count(DEPTNO) > 1;
```
### Output:
| "DEPTNO" | "COUNT(DEPTNO)" |
|----------|-----------------|
| "30" | "2" |
**17. To display Employee Names that has exactly four characters**
### Query:
```sql=
select ENAME, sum(char_length(ENAME))
from EMPLOYEE group by ENAME
having sum(char_length(ENAME)) = 4;
```
### Output:
| "ENAME" | "sum(char_length(ENAME))" |
|---------|---------------------------|
| "Mark" | "4" |
**18. To display ENAME that does not start with the letter “S**
### Query:
```sql=
select ENAME from employee where ENAME not like 'S%';
```
### Output:
| "ENAME" |
|------------------|
| "Jane Clark" |
| "Jack Smith" |
| "Mark" |
| "Antony Dolokov" |
| "Leo Tolstoy" |
| "Vladmir" |
| "Donald Trump" |
**19. To display department wise total amount spent on salary**
### Query:
```sql=
select DEPTNO, SUM(SALARY) from employee group by DEPTNO;
```
### Output:
| "DEPTNO" | "SUM(SALARY)" |
|----------|---------------|
| "30" | "99500" |
| "50" | "40000" |
| "60" | "38000" |
| "70" | "100000" |
| "10" | "25000" |
| "20" | "20000" |
**20. To display number of male and female employees in each department**
### Query:
```sql=
select DEPTNO, sum(case when gender = 'M'
then 1 else 0 end) males, sum(case when gender = 'F'
then 1 else 0 end) females from employee group by DEPTNO;
```
### Output:
| "DEPTNO" | "males" | "females" |
|----------|---------|-----------|
| "30" | "1" | "1" |
| "50" | "0" | "1" |
| "60" | "1" | "0" |
| "70" | "1" | "0" |
| "10" | "1" | "0" |
| "20" | "1" | "0" |
**21. To remove a tuple(s) of those employees who work departments 10 and 20(Use IN clause)**
### Query:
```sql=
delete from employee where DEPTNO in (10, 20);
```
### Output:
| "ENO" | "EName" | "GENDER" | "DEPTNO" | "JOB" | "DOJ" | "SALARY" | "CONTACT" |
|-------|------------------|----------|----------|-------|--------------|----------|-----------|
| "1" | "Jane Clark" | "F" | "30" | "CFO" | "2020-04-09" | "42500" | "null" |
| "2" | "Jack Smith" | "M" | "30" | "COO" | "2018-04-09" | "57000" | "null" |
| "3" | "Mark" | "F" | "50" | "VP" | "2000-03-04" | "40000" | "null" |
| "4" | "Antony Dolokov" | "M" | "60" | "PR" | "2004-08-03" | "38000" | "null" |
| "5" | "Leo Tolstoy" | "M" | "70" | "CEO" | "1999-09-02" | "100000" | "null" |
**22. To remove the attribute DOJ from the table Employee**
### Query:
```sql=
alter table EMPLOYEE drop column DOJ;
```
### Output:
| "ENO" | "EName" | "GENDER" | "DEPTNO" | "JOB" | "SALARY" | "CONTACT" |
|-------|------------------|----------|----------|-------|----------|-----------|
| "1" | "Jane Clark" | "F" | "30" | "CFO" | "42500" | "null" |
| "2" | "Jack Smith" | "M" | "30" | "COO" | "57000" | "null" |
| "3" | "Mark" | "F" | "50" | "VP" | "40000" | "null" |
| "4" | "Antony Dolokov" | "M" | "60" | "PR" | "38000" | "null" |
| "5" | "Leo Tolstoy" | "M" | "70" | "CEO" | "100000" | "null" |
**23. To write SQL Command that displays ENO, ENAME, DEPTNO and DEPTNAME using natural join of both the EMPLOYEE and DEPT tables**
### Query:
```sql=
select ENO, ENAME, DEPTNO, DEPTNAME
from EMPLOYEE natural join DEPT;
```
### Output:
| "ENO" | "ENAME" | "DEPTNO" | "DEPTNAME" |
|-------|------------------|----------|--------------|
| "1" | "Jane Clark" | "30" | "Management" |
| "4" | "Antony Dolokov" | "60" | "Admin" |
**24. To remove all tuples of the relation EMPLOYEE**
### Query:
```sql=
delete from EMPLOYEE;
```
**25. To remove the schema (or structure) of the relation EMPLOYEE**
### Query:
```sql=
drop database cspractical
```
Sign in with Wallet
Connect another wallet