SWASTIK COMPUTER EDUCATION

Q) Create database named as MEMBER using Ms-Access of following structure:

Field Name	Field type	Discription
mem_code	Short Text	Member code
mem_name	Short Text	Member name
mem_add	Short Text	Member address
mem_phone	Short Text	Member contact number
no_of_bk	Number	Number of books stock
no_of_iss	Number	Number of books issued

Q) Create database named as EMPOLYEE using Ms-Access of following structure:

Field name	Field type
NO	Number
NAME	Short Text
SALARY	Number
AREA	Short Text
AGE	Number
GRADE	Short Text
DEPT	Short Text

Q) Create database named as TOY SHOPPE  using Ms-Access of following structure:

Field name	Field type
Sno	Number
Toy name	Short Text
Category	Short Text
Price	Number
Quantity	Number
Starting_Age	Number
Ending_Age	Number

Q) Create database named as HOSPITAL  using Ms-Access of following structure:

No.	Name	Age	Department	Dateofadm	Charger	Sex
1	Arprit	62	Surgery	21/01/98	300	M
2	Zarina	22	ENT	12/12/97	250	F
3	Kareem	32	Orthopaedic	19/02/98	200	M
4	Arun	12	Surgery	11/01/98	300	M
5	Zubin	30	ENT	12/01/98	250	M
6	Ketaki	16	ENT	24/02/98	250	F
7	Ankita	29	Cardiology	20/02/98	800	F
8	Zareen	45	Gynaecology	22/02/98	300	F
9	Kush	19	Cardiology	13/01/98	800	M
10	Shi	23	Nuclear Medicine	21/02/98	400	F

Q) Create database named as STUDENT using Ms-Access of following structure:

No.	Name	Age	Department	Dateofadm	Fee	Sex
1	Pankaj	24	Computēr	10/01/97	120	M
2	Shalini	21	History	24/03/98	200	F
3	Sanjay	22	Hindi	12/12/96	300	M
4	Sudha	25	History	01/07/99	400	F
5	Rakesh	22	Hindi	05/09/97	250	M
6	Shakeel	30	History	27/06/98	300	M
7	Surya	34	Computer	25/02/97	210	M
8	Shikha	23	Hindi	31/07/97	200	F

Q) Create database named as EMPOLYEE using Ms-Access of following structure:

ENO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPINO
7369	Sunita sharma	CLERK	7902	17-DEC-80	2800	0	20
7499	Ashok Singhal	SALESMAN	7698	20-FEB-81	3600	300	30
7521	Rohit Rana	SALESMAN	7698	22-FEB-81	5250	500	30
7566	Jyoti Lamba	MANAGER	7839	02-APR-81	4975	0	20
7654	Martin S	SALESMAN	7698	28-SEP-81	6250	1400	30
7698	Binod Goel	MANAGER	7839	01-MAY-81	5850	0	30
7782	Chetan Gupta	MANAGER	7839	09-JUN-81	2450	0	10
7788	Sudhir Rawat	ANALYST	7566	19-APR-87	5000	0	20
7839	Kavita Sharma	PRESIDENT		17-NOV-81	5000	0	10
7844	Tushar Tiwari	SALESMAN	7698	08-SEP-81	4500	0	30
7876	Anand Rathi	CLERK	7788	23-MAY-87	6100	0	20
7900	Jagdeep Rana	CLERK	7698	03-DEC-81	4950	0	30
7902	Sumit Vats	ANALYST	7566	03-DEC-81	3500	3600	20
7934	Manoj Kaushik	CLERK	7782	23-JAN-82	5300	0	10

a)       To select all the columns of the above table.

b)      To list the name and employee number from the above table.

c)       To list all names , hiredate and salary off all employee.

d)      Display the ename and sal where comm is NULL.

e)      To list the salary where salary is less than the commission.

f)        To list the salary between 3000 and 4000.

g)       To list the mgr which are IN 7902, 7566;7788.

h)      To list the ename starting with ‘S’.

i)        To list the all the columns where salary is greater than 3100.

j)        list all the columns in the ascending order of hiredate.

k)       To list all the columns in the ascending order of deptno and descending order of salary .

l)        To display the employee name and job of employees hired between Feb 20, 1981 and May 1,1981.

m)    Display the ename and deptno of all employees in department 20 and 30 in alphabetical ORDER        BY name.

n)      To list the name and salary of all the employees who earn more than 1200 and are in department 10 or 40. 

o)      To list name and hiredate of all the employees who are hired in 1981.

p)      To list name and salary of all the employees who earn commissions.

q)      To list the names and job of all the employees who work in department 20 and their manager is 7788.