Experiment 07

Aim

To demonstrate the use of various DML operations i.e., SELECT statement, WHERE clause, AND, OR, NOT operators, ORDER BY, and INSERT SELECT statement

Theory

• SELECT statement:

  The SELECT statement is used to retrieve data from a database. It allows the user to specify which columns to retrieve and from which table.

  Syntax

  SELECT column1, column2, ...
  FROM table_name;

  To select all columns from a table:

  Syntax

  SELECT * FROM table_name;

• WHERE clause:

  The WHERE clause is used to filter records that meet a certain condition. It is used in conjunction with the SELECT, UPDATE, DELETE, etc. statements.

  Syntax

  SELECT * FROM table_name
  WHERE condition;

• AND, OR, NOT operators:

  The AND, OR, and NOT operators are used to combine multiple conditions in a WHERE clause.

  Syntax

  SELECT * FROM table_name
  WHERE condition1 AND condition2;
  
  SELECT * FROM table_name
  WHERE condition1 OR condition2;
  
  SELECT * FROM table_name
  WHERE NOT condition;

• ORDER BY clause:

  The ORDER BY clause is used to sort the result set in either ascending (ASC) or descending (DESC) order.

  Syntax

  SELECT * FROM table_name
  ORDER BY column_name ASC|DESC;

• INSERT SELECT statement:

  The INSERT SELECT statement is used to insert new records into a table by selecting data from another table.

  Syntax

  INSERT INTO table_name (column1, column2, ...)
  SELECT column1, column2, ...
  FROM another_table
  WHERE condition;

Commands

SELECT * FROM Employees; -- Draw the table "Before Insertion"
WHERE Age > 30 AND Department = 'HR'

ORDER BY LastName ASC;

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Department)
SELECT EmployeeID, FirstName, LastName, Age, Department
FROM AnotherEmployees
WHERE Age > 25;

SELECT * FROM Employees; -- Draw the table "After Insertion"

Conclusion

Successfully implemented SELECT statement, WHERE clause, AND, OR, NOT operators, ORDER BY clause, and INSERT SELECT statement (DML) commands