Scribd
Upload
5 views

Hour_2_SQL_Basics_Practice_With_Practice_Lines (1)

The document provides an overview of SQL basics, focusing on the SELECT statement, filtering data with WHERE, and limiting results using FETCH FIRST. It includes syntax examples and explanations for various SQL queries related to retrieving employee and department data. The document serves as a practical guide for practicing SQL queries.

Uploaded by

Satya sv
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
5 views

Hour_2_SQL_Basics_Practice_With_Practice_Lines (1)

The document provides an overview of SQL basics, focusing on the SELECT statement, filtering data with WHERE, and limiting results using FETCH FIRST. It includes syntax examples and explanations for various SQL queries related to retrieving employee and department data. The document serves as a practical guide for practicing SQL queries.

Uploaded by

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

Hour 2: SQL Basics - Practice Examples

1. SELECT Statement
Syntax: SELECT column1, column2 FROM table_name;
Examples:
1. SELECT first_name, last_name FROM employees;
Explanation: Retrieves the first and last names of all employees from the
employees table.
[ Practice Here: _______________________________________ ]
2. SELECT department_id FROM departments;
Explanation: Retrieves all department IDs from the departments table.
[ Practice Here: _______________________________________ ]
3. SELECT * FROM employees;
Explanation: Retrieves all columns and rows from the employees table.
[ Practice Here: _______________________________________ ]
4. SELECT salary FROM employees;
Explanation: Retrieves the salary column for all employees.
[ Practice Here: _______________________________________ ]
5. SELECT first_name || ' ' || last_name AS full_name FROM employees;
Explanation: Combines first name and last name with a space to form a full
name and renames the column as "full_name".
[ Practice Here: _______________________________________ ]
6. SELECT DISTINCT job_id FROM employees;
Explanation: Retrieves unique job IDs from the employees table.
[ Practice Here: _______________________________________ ]
7. SELECT COUNT(*) FROM employees;
Explanation: Counts the total number of employees in the employees table.
[ Practice Here: _______________________________________ ]
8. SELECT first_name, salary * 12 AS annual_salary FROM employees;
Explanation: Calculates annual salary by multiplying monthly salary by 12 and
renames the column as "annual_salary".
[ Practice Here: _______________________________________ ]
9. SELECT SYSDATE FROM dual;
Explanation: Retrieves the current date and time from the system.
[ Practice Here: _______________________________________ ]
10. SELECT 'Hello, Oracle SQL!' FROM dual;
Explanation: Outputs a static string "Hello, Oracle SQL!".
[ Practice Here: _______________________________________ ]

2. Filtering Data with WHERE


Syntax: SELECT column1 FROM table_name WHERE condition;
Examples:
1. SELECT * FROM employees WHERE salary > 50000;
Explanation: Retrieves all employees with a salary greater than 50,000.
[ Practice Here: _______________________________________ ]
2. SELECT first_name, last_name FROM employees WHERE department_id = 10;
Explanation: Retrieves first and last names of employees who work in
department 10.
[ Practice Here: _______________________________________ ]
3. SELECT * FROM employees WHERE hire_date >= '01-JAN-2020';
Explanation: Retrieves employees hired on or after January 1, 2020.
[ Practice Here: _______________________________________ ]
4. SELECT * FROM employees WHERE job_id = 'IT_PROG';
Explanation: Retrieves employees whose job ID is "IT_PROG".
[ Practice Here: _______________________________________ ]
5. SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;
Explanation: Retrieves employees with salaries between 40,000 and 60,000.
[ Practice Here: _______________________________________ ]
6. SELECT * FROM employees WHERE last_name LIKE 'A%';
Explanation: Retrieves employees whose last names start with the letter 'A'.
[ Practice Here: _______________________________________ ]
7. SELECT * FROM employees WHERE department_id IN (10, 20, 30);
Explanation: Retrieves employees who belong to departments 10, 20, or 30.
[ Practice Here: _______________________________________ ]
8. SELECT * FROM employees WHERE manager_id IS NULL;
Explanation: Retrieves employees who do not have a manager (manager_id is
null).
[ Practice Here: _______________________________________ ]
9. SELECT * FROM employees WHERE job_id != 'SA_REP';
Explanation: Retrieves employees whose job ID is not "SA_REP".
[ Practice Here: _______________________________________ ]
10. SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Explanation: Retrieves employees whose salaries are greater than the average
salary.
[ Practice Here: _______________________________________ ]

3. Limiting Results (FETCH FIRST)


Syntax: SELECT * FROM table_name FETCH FIRST n ROWS ONLY;
Examples:
1. SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;
Explanation: Retrieves the first 5 rows from the employees table.
[ Practice Here: _______________________________________ ]
2. SELECT * FROM employees ORDER BY hire_date FETCH FIRST 10 ROWS ONLY;
Explanation: Retrieves the 10 most recently hired employees.
[ Practice Here: _______________________________________ ]
3. SELECT first_name, salary FROM employees FETCH FIRST 3 ROWS ONLY;
Explanation: Retrieves the first 3 rows of employees' names and salaries.
[ Practice Here: _______________________________________ ]
4. SELECT department_id FROM departments FETCH FIRST 2 ROWS ONLY;
Explanation: Retrieves the first 2 department IDs from the departments table.
[ Practice Here: _______________________________________ ]
5. SELECT job_id FROM employees ORDER BY salary DESC FETCH FIRST 1 ROW ONLY;
Explanation: Retrieves the job ID of the employee with the highest salary.
[ Practice Here: _______________________________________ ]
6. SELECT DISTINCT department_id FROM employees FETCH FIRST 4 ROWS ONLY;
Explanation: Retrieves the first 4 unique department IDs from the employees
table.
[ Practice Here: _______________________________________ ]
7. SELECT * FROM employees WHERE salary > 50000 FETCH FIRST 7 ROWS ONLY;
Explanation: Retrieves the first 7 employees with salaries greater than
50,000.
[ Practice Here: _______________________________________ ]
8. SELECT * FROM employees ORDER BY last_name ASC FETCH FIRST 8 ROWS ONLY;
Explanation: Retrieves the first 8 employees sorted alphabetically by last
name.
[ Practice Here: _______________________________________ ]
9. SELECT job_id, COUNT(*) FROM employees GROUP BY job_id FETCH FIRST 5 ROWS
ONLY;
Explanation: Retrieves the first 5 job IDs along with the count of employees
for each job.
[ Practice Here: _______________________________________ ]
10. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id
FETCH FIRST 6 ROWS ONLY;
Explanation: Retrieves the first 6 departments along with their average
salary.
[ Practice Here: _______________________________________ ]

You might also like