DB queries

Sure, here are some example queries using various database constraints:

1. **Creating a Table with Primary Key Constraint**:

```sql
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
```

2. **Adding a Foreign Key Constraint**:

```sql
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
ProductID INT,
OrderDate DATE,
CONSTRAINT FK_ProductID FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
```

3. **Adding a Unique Constraint**:

```sql
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100) UNIQUE,
DepartmentID INT);
```
4. **Adding a Not Null Constraint**:
```sql
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100),
Age INT
);
```

5. **Adding a Check Constraint**:

```sql
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2),
Quantity INT,
CONSTRAINT CHK_Price CHECK (Price >= 0),
CONSTRAINT CHK_Quantity CHECK (Quantity >= 0)
);
```

6. **Dropping a Constraint**:
```sql
ALTER TABLE Employees
DROP CONSTRAINT FK_DepartmentID;
```

7. **Enabling/Disabling Constraints**:
```sql
ALTER TABLE Orders
DISABLE CONSTRAINT FK_ProductID;
ALTER TABLE Orders
ENABLE CONSTRAINT FK_ProductID;
```
Joins in DB:
Joins are used to combine rows from two or more tables based on a related column between them.
Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. Here's an
example of an INNER JOIN:

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Add, Create, Delete, Update, Truncate, Drop Queries:

These are Data Manipulation Language (DML) commands used to interact with data in tables.
 INSERT: Adds new rows to a table.
 CREATE TABLE: Creates a new table in the database.
 DELETE: Removes rows from a table based on a condition.
 UPDATE: Modifies existing rows in a table.
 TRUNCATE TABLE: Deletes all rows from a table (without logging individual row
deletions).
 DROP TABLE: Deletes an entire table from the database.
Max/Min, Count, OrderBy, SortBy, GroupBy:
These are commonly used in combination with SELECT statements to retrieve and manipulate data.
 MAX/MIN: Returns the maximum or minimum value from a column.
 COUNT: Returns the number of rows that match a specified condition.
 ORDER BY: Sorts the result set based on one or more columns, either in ascending or
descending order.
 SORT BY: There's no specific "SORT BY" clause in SQL; "ORDER BY" is used for sorting.
 GROUP BY: Groups rows that have the same values into summary rows, typically used with
aggregate functions like SUM, AVG, COUNT, etc.
Example usage:

SELECT MAX(Salary) AS MaxSalary FROM Employees;

SELECT Department, COUNT(*) AS EmployeeCount FROM Employees GROUP BY Department;

SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC;

SELECT Department, AVG(Salary) AS AvgSalary FROM Employees GROUP BY Department;

ADD -- Adds a new column to an existing table

ADD CONSTRAINT -- Creates a new constraint on an existing table, which is used to specify rules
for any data in the table.

ALTER TABLE -- Adds, deletes or edits columns in a table. It can also be used to add and delete
constraints in a table, as per the above.

ALTER COLUMN -- Changes the data type of a table’s column.

ALL -- Returns true if all of the subquery values meet the passed condition.

AND -- Used to join separate conditions within a WHERE clause.

ANY -- Returns true if any of the subquery values meet the given condition.

AS -- Renames a table or column with an alias value which only exists for the duration of the query.

ASC -- Used with ORDER BY to return the data in ascending order.

BETWEEN -- Selects values within the given range.

CASE -- Changes query output depending on conditions.

CHECK -- Adds a constraint that limits the value which can be added to a column.

CREATE DATABASE -- Creates a new database.

CREATE TABLE -- Creates a new table.

DEFAULT -- Sets a default value for a column

DELETE -- Delete data from a table.

DESC -- Used with ORDER BY to return the data in descending order.

DROP COLUMN -- Deletes a column from a table.

DROP DATABASE -- Deletes the entire database.

DROP DEAFULT -- Removes a default value for a column.

DROP TABLE -- Deletes a table from a database.

EXISTS -- Checks for the existence of any record within the subquery, returning true if one or more
records are returned.

FROM -- Specifies which table to select or delete data from.

IN -- Used alongside a WHERE clause as a shorthand for multiple OR conditions.

INSERT INTO -- Adds new rows to a table.

IS NULL -- Tests for empty (NULL) values.

IS NOT NULL -- The reverse of NULL. Tests for values that aren’t empty / NULL.

LIKE -- Returns true if the operand value matches a pattern.

NOT -- Returns true if a record DOESN’T meet the condition.

OR -- Used alongside WHERE to include data when either condition is true.

ORDER BY -- Used to sort the result data in ascending (default) or descending order through the use
of ASC or DESC keywords.

ROWNUM -- Returns results where the row number meets the passed condition.

SELECT -- Used to select data from a database, which is then returned in a results set.

SELECT DISTINCT -- Sames as SELECT, except duplicate values are excluded.

SELECT INTO -- Copies data from one table and inserts it into another.

SELECT TOP -- Allows you to return a set number of records to return from a table.

SET -- Used alongside UPDATE to update existing data in a table.

SOME -- Identical to ANY.

TOP -- Used alongside SELECT to return a set number of records from a table.

TRUNCATE TABLE -- Similar to DROP, but instead of deleting the table and its data, this deletes
only the data.

UNION -- Combines the results from 2 or more SELECT statements and returns only distinct values.

UNION ALL -- The same as UNION, but includes duplicate values.

UNIQUE -- This constraint ensures all values in a column are unique.

UPDATE -- Updates existing data in a table.

VALUES -- Used alongside the INSERT INTO keyword to add new values to a table.
WHERE -- Filters results to only include data which meets the given condition.

Table :- EmpProject

EmpId ProjectId ClientID StartYear EndYear

101 p-1 Cl-1 2010 2010
102 p-2 Cl-2 2010 2012
103 p-1 Cl-3 2013
104 p-4 Cl-1 2014 2015
105 p-4 Cl-5 2015

Schema:-
create table empproject(empid int foreign key references employee(empid),
projectid varchar(50) foreign key references project(projectid), clientid
varchar(50) foreign key references clienttable(clientid),startyear int, endyear
int)

Table :- EmpDept

DeptId DeptName Dept_off DeptHead

E-101 HR Monday 105

E-102 Development Tuesday 101

E-103 Hous Keeping Saturday 103

E-104 Sales Sunday 104

E-105 Purchage Tuesday 104

Schema:-
create table empdept(deptid varchar(50) primary key,deptname varchar(100),
dept_off varchar(100), depthead int foreign key references employee(empid))

Simple Queries
1. Select the detail of the employee whose name
start with P.

select * from employee where empname like 'p%'

How many permanent candidate take salary more

than 5000.

select count(salary) as count from empsalary where ispermanent='yes' and

salary>5000

Select the details of the employee who work either

for department E-104 or E-102.

select * from employee where department='E-102' or department='E-104'

or

select * from employee where department in ('E-102','E-104')

What is total salarythat is paid to permanent

employees?

select sum(salary) as salary from empsalary where ispermanent='yes'

List name of all employees whose name ends with a.

select * from employee where empname like '%a'

How many project started in year 2010.

select count(projectid) as project from empproject where startyear=2010

select the details of all employee working in

development department.

select * from employee where department in(select deptid from empdept where
deptname='development')