Sure, here are the SQL commands for each part of the question:
### 1. Create Database and Tables
**Create Database:**
```sql
-- SQLite does not support the CREATE DATABASE statement.
-- Instead, the database is created when you connect to a new database file.
-- Assuming the database file name is "WORK.db".
```
**Create Tables:**
```sql
CREATE TABLE IF NOT EXISTS Employee (
Emp_no INTEGER PRIMARY KEY,
Emp_name TEXT NOT NULL,
Age INTEGER NOT NULL,
Dept_code INTEGER NOT NULL,
Mngr_no INTEGER,
Salary INTEGER NOT NULL,
FOREIGN KEY (Dept_code) REFERENCES Dept(Dept_no),
FOREIGN KEY (Mngr_no) REFERENCES Employee(Emp_no)
);
CREATE TABLE IF NOT EXISTS Dept (
Dept_no INTEGER PRIMARY KEY,
Dept_name TEXT CHECK(Dept_name IN ('Accounts', 'Marketing', 'Finance')) NOT NULL,
Mngr_no INTEGER NOT NULL,
FOREIGN KEY (Mngr_no) REFERENCES Employee(Emp_no)
);
```
### 2. Insert Records into Tables
**Insert Records into Employee Table:**
```sql
INSERT INTO Employee (Emp_no, Emp_name, Age, Dept_code, Mngr_no, Salary) VALUES
(1, 'Alice', 30, 1, NULL, 50000),
(2, 'Bob', 25, 2, 1, 40000),
(3, 'Charlie', 28, 3, 1, 35000),
(4, 'David', 35, 1, 1, 45000),
(5, 'Eva', 40, 2, 4, 38000),
(6, 'Frank', 45, 3, 4, 37000),
(7, 'Grace', 50, 1, 1, 48000),
(8, 'Hank', 55, 2, 1, 52000),
(9, 'Ivy', 32, 3, 1, 41000),
(10, 'Jack', 36, 2, 4, 43000);
```
**Insert Records into Dept Table:**
```sql
INSERT INTO Dept (Dept_no, Dept_name, Mngr_no) VALUES
(1, 'Accounts', 1),
(2, 'Marketing', 4),
(3, 'Finance', 3);
```
### 3. Queries to Fulfill Given Tasks
**Query 5: Display names of employees who are working as managers**
```sql
SELECT Emp_name FROM Employee
WHERE Emp_no IN (SELECT Mngr_no FROM Dept);
```
**Query 6: Display names of employees and their department name**
```sql
SELECT Employee.Emp_name, Dept.Dept_name
FROM Employee
JOIN Dept ON Employee.Dept_code = Dept.Dept_no;
```
**Query 7: Display names of employees and their department name where they are working**
(Same as Query 6)
```sql
SELECT Employee.Emp_name, Dept.Dept_name
FROM Employee
JOIN Dept ON Employee.Dept_code = Dept.Dept_no;
```
**Query 8: Display names of employees in ascending order based on their age**
```sql
SELECT Emp_name FROM Employee
ORDER BY Age ASC;
```
**Query 9: Display details of employees whose salaries are within range of 18,000 to 38,000**
```sql
SELECT * FROM Employee
WHERE Salary BETWEEN 18000 AND 38000;
```
**Query 10: Display name, department_name and salary of managers in ascending order salary
wise**
```sql
SELECT Employee.Emp_name, Dept.Dept_name, Employee.Salary
FROM Employee
JOIN Dept ON Employee.Emp_no = Dept.Mngr_no
ORDER BY Employee.Salary ASC;
```
**Query 11: Name the employees who are earning more than the average salaries of employees**
```sql
SELECT Emp_name FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);
```
**Query 12: Name the employees who are earning more than the salary of their manager**
```sql
SELECT E1.Emp_name FROM Employee E1
JOIN Employee E2 ON E1.Mngr_no = E2.Emp_no
WHERE E1.Salary > E2.Salary;
```
These SQL commands will create the required tables, insert the specified records, and perform the
queries to meet the given tasks. Make sure to run these queries within a SQL environment
connected to the `WORK.db` database.
To accomplish the tasks you’ve outlined, follow these steps. I’ll use a hypothetical database name,
`SchoolDB`, but you can replace it with your preferred name.
### 1. Create a Database and Table
First, create the database and table. Here's the SQL to create the database and the `Class` table:
```sql
-- Create the database
CREATE DATABASE SchoolDB;
-- Use the database
USE SchoolDB;
-- Create the table with appropriate constraints
CREATE TABLE Class (
C_no INT AUTO_INCREMENT PRIMARY KEY,
Branch ENUM('BBA', 'BCA', 'B.com', 'BA') NOT NULL,
Year ENUM('FY', 'SY', 'TY') NOT NULL,
Total_student INT NOT NULL,
CONSTRAINT chk_total_student CHECK (Total_student >= 0)
);
```
### 2. Insert Records
Insert at least 10 records into the `Class` table:
```sql
INSERT INTO Class (Branch, Year, Total_student) VALUES
('BBA', 'FY', 120),
('BBA', 'SY', 110),
('BBA', 'TY', 130),
('BCA', 'FY', 140),
('BCA', 'SY', 115),
('BCA', 'TY', 125),
('B.com', 'FY', 135),
('B.com', 'SY', 125),
('B.com', 'TY', 140),
('BA', 'FY', 100);
```
### 3. Display Branch and Total Strength for a Particular Branch
To display the branch and total strength for a particular branch (e.g., BCA):
```sql
SELECT Branch, SUM(Total_student) AS Total_strength
FROM Class
WHERE Branch = 'BCA'
GROUP BY Branch;
```
### 4. Display Year-wise Total Strength of Students
To display the total strength of students year-wise:
```sql
SELECT Year, SUM(Total_student) AS Total_strength
FROM Class
GROUP BY Year;
```
### 5. Display Year and Strength for BCA in Ascending Order of Strength
To display year and strength for BCA in ascending order of strength:
```sql
SELECT Year, Total_student
FROM Class
WHERE Branch = 'BCA'
ORDER BY Total_student ASC;
```
### 6. Display Branch and Year Where Strength is More Than 110
To display the branch and year for those years where strength is more than 110:
```sql
SELECT Branch, Year
FROM Class
WHERE Total_student > 110;
```
### 7. Display Branch and Total Strength Where Total Strength is More Than 400
To display the branch and total strength where total strength is more than 400:
```sql
SELECT Branch, SUM(Total_student) AS Total_strength
FROM Class
GROUP BY Branch
HAVING SUM(Total_student) > 400;
```
### 8. Display Total Strength for All Branches Except BBA
To display the total strength for all branches except BBA:
```sql
SELECT Branch, SUM(Total_student) AS Total_strength
FROM Class
WHERE Branch != 'BBA'
GROUP BY Branch;
```
### 9. Display Branch, Year, and Total Strength Where First Letter of Branch Starts with ‘B’
To display branch, year, and total strength for branches where the first letter is ‘B’:
```sql
SELECT Branch, Year, Total_student
FROM Class
WHERE Branch LIKE 'B%';
```
### 10. Display Branch, Year, and Total Strength Where Second Letter of Branch Starts with ‘C’
To display branch, year, and total strength where the second letter is ‘C’:
```sql
SELECT Branch, Year, Total_student
FROM Class
WHERE Branch LIKE '_C%';
```
Replace the placeholders and adjust the queries as needed based on your actual requirements.
