ORACLE DATABASE WITH SQL QUERY FOR (KIIT) DBMS

Sangram panda blogg

Instagram

You can Contribute

Comment in Below ⬇️ Your query Will be updated

              
    

 

  DBMS LAB ALL Query 
   

# Dataabse managemant System 


1. SELECT * FROM employees;

2. SELECT DISTINCT department_id FROM employees;

3. SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct = 0.2;

4. SELECT first_name, department_id, salary * 0.15 AS "TA", salary * 0.4 AS "DA", salary * 0.32 AS "HRA", salary * (0.87) + salary AS "GROSS SALARY"
FROM employees;

Certainly, here are your SQL queries separated and explained individually:

5. **Select last names and job IDs of employees with managers (where manager_id is not null)**:
   ```sql
   SELECT last_name, job_id
   FROM employees
   WHERE manager_id IS NOT NULL;
   ```

6. **Select specific columns for employees with a non-null commission percentage, ordered by salary**:
   ```sql
   SELECT first_name, last_name, department_id, salary, commission_pct
   FROM employees
   WHERE commission_pct IS NOT NULL
   ORDER BY salary;
   ```

7. **Select last names and department IDs for employees in departments 30 and 50, ordered by last names**:
   ```sql
   SELECT last_name, department_id
   FROM employees
   WHERE department_id IN (30, 50)
   ORDER BY last_name;
   ```

8. **Select last names, department IDs, job IDs, and hire dates for employees hired between '20-Feb-1998' and '01-June-1999'**:
   ```sql
   SELECT last_name, department_id, job_id, hire_date
   FROM employees
   WHERE hire_date >= '20-Feb-1998' AND hire_date < '01-June-1999';
   ```

9. **Select last names of employees with first names that have "a" as the second character (using the underscore "_" as a wildcard for the first character)**:
   ```sql
   SELECT last_name
   FROM employees
   WHERE first_name LIKE '_a%';
   ```

10. **Select last names of employees with both "i" and "o" in their last names**:
   ```sql
   SELECT last_name
   FROM employees
   WHERE last_name LIKE '%i%' AND last_name LIKE '%o%';
   ```

11. **Select first names and hire dates of employees hired between '01-Jan-1999' and '31-Dec-1999'**:
   ```sql
   SELECT first_name, hire_date
   FROM employees
   WHERE hire_date >= '01-Jan-1999' AND hire_date <= '31-Dec-1999';
   ```

You can run each of these SQL queries separately in your database management system to retrieve the desired results for each query.

23 August 2023 at 22:17

12. **Select first name, last name, salary, and department ID of all employees**:
    ```sql
    SELECT first_name, last_name, salary, department_id
    FROM employees;
    ```

13. **Select only the department IDs of all employees**:
    ```sql
    SELECT department_id
    FROM employees;
    ```

14. **Select distinct department IDs from the employees table**:
    ```sql
    SELECT DISTINCT department_id
    FROM employees;
    ```

15. **Select all columns for employees with a salary greater than 10,000**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary > 10000;
    ```

16. **Select all columns for employees with a salary greater than or equal to 10,000**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary >= 10000;
    ```

17. **Select all columns for employees with a salary less than 10,000**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary < 10000;
    ```

18. **Select all columns for employees with a salary less than or equal to 10,000**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary <= 10000;
    ```

19. **Select all columns for employees with a salary not equal to 10,000**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary <> 10000;
    ```

20. **Select all columns for employees with a salary equal to 10,000**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary = 10000;
    ```

21. **Select all columns for employees with the last name 'King'**:
    ```sql
    SELECT *
    FROM employees
    WHERE last_name = 'King';
    ```


21. **Select * from employees where department_id > ANY(90,100)**:
    ```sql
    SELECT *
    FROM employees
    WHERE department_id > ANY(90,100);
    ```

22. **Select * from employees where email='SKING'**:
    ```sql
    SELECT *
    FROM employees
    WHERE email = 'SKING';
    ```

23. **Select * from employees where salary > any(4000,6000,11000)**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary > ANY(4000, 6000, 11000);
    ```

24. **Select * from employees where salary > all(4000,6000,11000)**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary > ALL(4000, 6000, 11000);
    ```

25. **Select * from employees where salary in(4100,9500,17000,24000)**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary IN (4100, 9500, 17000, 24000);
    ```

26. **Select * from employees where first_name in('Steven','Neena')**:
    ```sql
    SELECT *
    FROM employees
    WHERE first_name IN ('Steven', 'Neena');
    ```

27. **Select * from employees where salary between 10000 and 24000**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary BETWEEN 10000 AND 24000;
    ```

28. **Select * from employees where hire_date between '12-aug-1987' and '01-jan-2000'**:
    ```sql
    SELECT *
    FROM employees
    WHERE hire_date BETWEEN '12-aug-1987' AND '01-jan-2000';
    ```

29. **Select * from employees where first_name like 'Sh%'**:
    ```sql
    SELECT *
    FROM employees
    WHERE first_name LIKE 'Sh%';
    ```

30. **Select * from employees where first_name like '%s'**:
    ```sql
    SELECT *
    FROM employees
    WHERE first_name LIKE '%s';
    ```

31. **Select * from employees where salary between**:
    It appears that this query is incomplete. Please specify the salary range you want to use in the `BETWEEN` clause.

32. **Select * from employees where salary >= 5000 and hire_date <= '17-jun-87'**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary >= 5000 AND hire_date <= '17-jun-87';
    ```

33. **Select * from employees where salary >= 5000 and job_id = 'IT_PROG'**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary >= 5000 AND job_id = 'IT_PROG';
    ```

34. **Select employee_id, salary + COMMISSION_PCT * salary from employees**:
    ```sql
    SELECT employee_id, salary + (COMMISSION_PCT * salary) AS "Total Earnings"
    FROM employees;
    ```

35. **Select employee_id, first_name, salary * 0.1 as "HRA" from employees**:
    ```sql
    SELECT employee_id, first_name, salary * 0.1 as "HRA"
    FROM employees;
    ```

36. **Select * from employees order by salary desc**:
    ```sql
    SELECT *
    FROM employees
    ORDER BY salary DESC;
    ```

37. **Select * from employees order by salary**:
    ```sql
    SELECT *
    FROM employees
    ORDER BY salary;
    ```

38. **Select employee_id, first_name, salary * 0.15 as "TA" from employees**:
    ```sql
    SELECT employee_id, first_name, salary * 0.15 as "TA"
    FROM employees;
    ```
28 August 2023 at 03:13

39. **Select * from employees where salary >= 5000 and hire_date >= '01-jan-2000'**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary >= 5000 AND hire_date >= '01-jan-2000';
    ```

40. **Select * from employees where salary >= 5000 and job_id = 'SA_MAN'**:
    ```sql
    SELECT *
    FROM employees
    WHERE salary >= 5000 AND job_id = 'SA_MAN';
    ```

41. **Select employee_id, first_name, salary + commission_pct * salary from employees**:
    ```sql
    SELECT employee_id, first_name, salary + (commission_pct * salary) AS "Total Earnings"
    FROM employees;
    ```

42. **Select employee_id, first_name, salary, salary * 0.1 as "HRA" from employees**:
    ```sql
    SELECT employee_id, first_name, salary, salary * 0.1 as "HRA"
    FROM employees;
    ```

43. **Select * from employees where hire_date <= '01-jan-2020'**:
    ```sql
    SELECT *
    FROM employees
    WHERE hire_date <= '01-jan-2020';
    ```

44. **Select * from employees order by salary**:
    ```sql
    SELECT *
    FROM employees
    ORDER BY salary;
    ```

45. **Select * from employees order by salary desc**:
    ```sql
    SELECT *
    FROM employees
    ORDER BY salary DESC;
    ```

46. **Select distinct job_id from employees**:
    ```sql
    SELECT DISTINCT job_id
    FROM employees;
    ```

47. **Select employee_id, commission_pct from employees where commission_pct is not null**:
    ```sql
    SELECT employee_id, commission_pct
    FROM employees
    WHERE commission_pct IS NOT NULL;
    ```
    

    
Aggregate function in sql (04-09-2023)
    
1. Get the current date and time:
```sql
SELECT SYSDATE FROM DUAL;
```

2. Extract the year from the current date:
```sql
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
```

3. Add 12 months to a specific date ('01-jan-2020'):
```sql
SELECT ADD_MONTHS(TO_DATE('01-jan-2020', 'dd-mon-yyyy'), 12) FROM DUAL;
```

4. Get the last day of a specific date ('01-jan-2020'):
```sql
SELECT LAST_DAY(TO_DATE('01-jan-2020', 'dd-mon-yyyy')) FROM DUAL;
```

5. Assuming you have an 'employees' table and want to retrieve the hire date and the last day of '01-jan-2020':
```sql
SELECT hire_date, LAST_DAY(TO_DATE('01-jan-2020', 'dd-mon-yyyy')) FROM employees;
```

6. Calculate the number of months between the current date and '01-jan-2023':
```sql
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('01-jan-2023', 'dd-mon-yyyy')) FROM DUAL;
```

1. Get the total number of employees:
```sql
SELECT COUNT(*) FROM employees;
```

2. Get the count of non-null job IDs for employees:
```sql
SELECT COUNT(job_id) FROM employees;
```

3. Get the count of distinct job IDs for employees:
```sql
SELECT COUNT(DISTINCT job_id) FROM employees;
```

4. Find the minimum salary among all employees:
```sql
SELECT MIN(salary) FROM employees;
```

5. Find the maximum salary among all employees:
```sql
SELECT MAX(salary) FROM employees;
```

6. Find the minimum salary for each job ID:
```sql
SELECT job_id, MIN(salary) FROM employees GROUP BY job_id;
```

7. Find the maximum salary for each job ID:
```sql
SELECT job_id, MAX(salary) FROM employees GROUP BY job_id;
```

8. Find the average salary for each department where the department ID is not null:
```sql
SELECT department_id, AVG(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id;
```
    
Alternative SQL in sql (09-10-2023)
  1**
CREATE TABLE Workers(Empno number(4),Name varchar2(122),Job varchar2(55),Mgr number(10),salary number(10));
**

2 **ALTER TABLE Workers
ADD commission number;
**

3** ALTER TABLE Workers
RENAME column job to designation;
**

4 ** ALTER TABLE Workers
 DROP column commission; 
**

5** 
ALTER TABLE Workers
ADD CONSTRAINT not_null
CHECK (Name IS NOT NULL AND Empno IS NOT NULL AND Salary IS NOT NULL);

**

6 ** ALTER TABLE Workers
ADD  UNIQUE (Empno);
**

7 ** ALTER TABLE Workers
ADD PRIMARY KEY (Empno);
**

8 **
insert all
into Workers values(69,'Sangram Panda','Programer',234,1200)
into Workers values(68,'Sangbeet Roy','Programer',234,18000)
into Workers values(67,'Sangram Tripathi','Programerhead',231,120000)
into Workers values(66,'Satya narayan misra','jrProgarmer',231,120000)
into Workers values(65,'Soumya Ranjan Panda','developer',234,13000)

select * from dual;
** 

9 **
UPDATE  Workers
SET Salary = Salary * 1.10;
**

10 **
ALTER TABLE Workers
MODIFY Salary number NULL;
**

 
Update & Delete  (02-11-2023)
 1. DROP TABLE Sangram

2. CREATE TABLE Sangram AS SELECT * FROM employees

3. UPDATE Sangram SET JOB_ID='Manager' WHERE First_name='Neena'

4. UPDATE Sangram SET salary=12345 WHERE First_name='Neena'

5. UPDATE Sangram SET Salary=salary+0.1*salary

6. UPDATE Sangram SET JOB_ID='Se_Manager' WHERE First_name='Neena'

7. DELETE FROM Sangram WHERE JOB_ID='Manager'

8. DELETE FROM Sangram WHERE EMPLOYEE_ID IN (101, 105, 107)

9. UPDATE Sangram SET hire_date='15-MAY-2021' WHERE EMPLOYEE_ID=119

10. DELETE FROM Sangram WHERE hire_date>'01-JAN-2021' AND MANAGER_ID IS NOT NULL

11. UPDATE Sangram SET Salary=salary+0.2*salary WHERE JOB_ID LIKE '%man'

12. UPDATE Sangram SET COMMISSION_PCT=0.15*salary WHERE hire_date > '1-JAN-2015'