Scribd
Upload
4 views8 pages

Assignment1 Python

The document outlines SQL queries for creating and populating 'department' and 'employee' tables, including data types and primary/foreign key relationships. It also includes various SELECT queries to retrieve employee information based on different criteria, such as salary, name patterns, and department associations. Additionally, it demonstrates the use of SQL operations like UNION, INTERSECT, and EXCEPT to manipulate and query the data effectively.

Uploaded by

gamopatel671
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views8 pages

Assignment1 Python

The document outlines SQL queries for creating and populating 'department' and 'employee' tables, including data types and primary/foreign key relationships. It also includes various SELECT queries to retrieve employee information based on different criteria, such as salary, name patterns, and department associations. Additionally, it demonstrates the use of SQL operations like UNION, INTERSECT, and EXCEPT to manipulate and query the data effectively.

Uploaded by

gamopatel671
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 8

Martin Patel Roll No:-55 DIV-1

ASSIGNMENT-1
Q-1 Create table “department”

Column name Datatype


Dept_id Integer primary key
Dept_name text
QUERY: create table department (dept_id integer primary key, dept_name text);

Q-2 Create table “employee” with following columns:

Column name Datatype


emp_id Integer primary key
First_name Text
last_name Text
Joining_date Text
Salary Numeric
Dept_id Text(foreign key department(dept_id)
QUERY: create table employee (emp_id int primary key, first_name text, last_name text,
joining_date text, salary numeric, dept_id int,foreign key (dept_id) references
department(dept_id));

Q-3 insert following records in department table.

Dept_id Dept_name
1 Account
2 Data entry
3 HR
4 Engineering
5 Finance
6 Loan
QUERY: sqlite> insert into department (dept_id,dept_name) values (1,'Account');
sqlite> insert into department (dept_id,dept_name) values (2,'Data entry');
sqlite> insert into department (dept_id,dept_name) values (3,'HR');
sqlite> insert into department (dept_id,dept_name) values (4,'Engineering');
sqlite> insert into department (dept_id,dept_name) values (5,'Finance');
sqlite> insert into department (dept_id,dept_name) values (6,'Loan');

Page 1|8
Martin Patel Roll No:-55 DIV-1

Q-4: Insert following records in employee table.


Emp_id First_name Last_name Joining_date salary Dept_id
1 Hiteshree Mistry 1/7/2023 25000.00 1
2 Deepa Mohanto 2/7/2023 23000.00 2
3 Dipti Patel 3/4/2022 45000.00 3
4 Sonali Mistry 13/11/2024 67000.00 5
5 Nikita Rathod 2/5/2009 12500.00 4
6 Hetvi Mistry 15/12/2020 12500.00 2
7 Darshana Patel 13/3/2023 34000.00 1
8 Krunal Bhavsar 2/4/2024 5
9 Darshan Desai 4/5/2022 65000.00 3
10 Hetvi Patel 3/5/2021 4

QUERY:
sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)
values (1,'hiteshree','mistry','1/7/2023','25000','1');
sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)
values (2,'deepa','mohanto','2/7/2023','23000','2');
sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)
values (3,'dipti','patel','3/4/2022','45000','3');
sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)
values (4,'sonali','mistry','13/11/2024','67000','5');
sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)
values (5,'nikita','rathod','2/5/2009','12500','4');
sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)
values (2,'hetvi','mistry','15/12/2020','12500','2');
Runtime error: UNIQUE constraint failed: employee.emp_id (19)
sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)
values (6,'hetvi','mistry','15/12/2020','12500','2');
sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)
values (7,'darshana','patel','13/3/2023','34000','1');
sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)
values (8,'krunal','bhavsar','2/4/2024',null,'5');

Page 2|8
Martin Patel Roll No:-55 DIV-1

sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)


values (9,'darshan','desai','4/5/2022','65000','3');
sqlite> insert into employee (emp_id,first_name,last_name,joining_date,salary,dept_id)
values (10,'hetvi','patel','3/5/2021',null,'4');

OUTPUT:

Q-5: Select distinct last_name from employee table.


QUERY:
select last_name from employee;
OUTPUT:

6. Select all the employees having salary >=25000 from employee table.
QUERY:
select * from employee where salary >=25000;

Page 3|8
Martin Patel Roll No:-55 DIV-1

7. Select all the employees having salary >=25000 and dept_id = 3 from employee table.
QUERY:
select * from employee where salary >=25000 and dept_id =3;

OUTPUT:

8. Find all the employees having salary between 20000 and 50000 from employee table
using between operator
QUERY:
select * from employee where salary between 20000 and 50000;
OUTPUT:

9. Find all the employees having salary between 23000 and 25000 from employee table
using in operator.
QUERY: select * from employee where salary in(23000,25000);
OUTPUT:

10. Select all the employees whose first name starts from d from employee table.

Page 4|8
Martin Patel Roll No:-55 DIV-1

QUERY:
select * from employee where first_name like 'd%';
OUTPUT:

11. Select all the employees whose last name starts from m and ends with y from
employee table.
OUERY:
select * from employee where last_name like 'm%y';
OUTPUT:

12. Select all the employees whose first name starts from d and ends with a from
employee table.
QUERY:
select * from employee where first_name like 'd%a';
OUTPUT:

13. Select all the employees whose first name contains substring ‘he’ or ‘na’.
QEURY:
select * from employee where first_name like '%he%' or first_name like '%na%';
OUTPUT:

Page 5|8
Martin Patel Roll No:-55 DIV-1

14. Select all the employees whose first name contains following pattern : ‘sh_’.
QUERY:
select * from employee where first_name like '%sh_%';
OUTPUT:

15. Select employee id, first name, last name from employee table and department name
from department table. Apply union to find entries.
QUERY:
select emp_id,first_name,last_name from employee
...> union
...> select dept_id,dept_name,null from department;
OUTPUT:

Page 6|8
Martin Patel Roll No:-55 DIV-1

16. Find list all the department names for which employee is working. (use intersection).
QUERY:
select dept_id from department intersect Select dept_id from employee;
OUTPUT:

17. Find list of all the department names for which none of the employee is working. (use
except).
QUERY:
select dept_name from department where dept_id not in (select DISTINCT dept_id from
employee);
OUTPUT:

18. Select first five employees.


QUERY:
select * from employee limit 5;
OUTPUT:

19. Select three employees starting from fifth position


QUERY:
select * from employee limit 3 offset 4;
OUTPUT:

Page 7|8
Martin Patel Roll No:-55 DIV-1

20. Select list of employees whose salary is not decided yet.


QUERY:
select * from employee where salary is null;
OUTPUT:

Page 8|8

You might also like