---
# System prepended metadata

title: CS RECORD PRACTICALS

---

# 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
```