Write SQL queries for given Database , Find name of employee having first letter ‘S’ in their names

Question

Write SQL queries for given Database 1.

Employeee(eid,empname,street,city)
Works(eid,cid,salary)
Company(cid,comapanyname,city)
Manager(eid,managername)

1. Find name of employee having first letter ‘S’ in their names
2. Display Annual salary of employee
3. Find name,street,city of employee who work for “Accenture” and
Earn more than 30000
4. Give total no of employees 

Answer 1

1. To find the name of employees having the first letter 'S' in their names, we can use the following SQL query:

SELECT empname
FROM Employeee
WHERE empname LIKE 'S%'

2. To display the annual salary of employees, we need to calculate the total salary earned by each employee in a year. Assuming that the salary column in the Works table is the monthly salary, we can use the following SQL query:

SELECT empname, salary*12 AS annual_salary
FROM Employeee e
JOIN Works w ON e.eid = w.eid

3. To find the name, street, and city of employees who work for "Accenture" and earn more than 30000, we can use the following SQL query:

SELECT empname, street, city
FROM Employeee e
JOIN Works w ON e.eid = w.eid
JOIN Company c ON w.cid = c.cid
WHERE companyname = 'Accenture' AND salary > 30000

4. To give the total number of employees, we can use the following SQL query:

SELECT COUNT(*) AS total_employees
FROM Employeee

A sample database to work on with this operations . 

-- Create the Employeee table
CREATE TABLE Employeee (
    eid INT PRIMARY KEY,
    empname VARCHAR(50),
    street VARCHAR(50),
    city VARCHAR(50)
);

-- Insert some sample data into the Employeee table
INSERT INTO Employeee (eid, empname, street, city) VALUES
    (1, 'John Doe', '123 Main St', 'New York'),
    (2, 'Mary Smith', '456 Elm St', 'Boston'),
    (3, 'Sam Johnson', '789 Maple St', 'Chicago'),
    (4, 'Samantha Williams', '321 Oak St', 'San Francisco'),
    (5, 'George Davis', '555 Pine St', 'Los Angeles');

-- Create the Company table
CREATE TABLE Company (
    cid INT PRIMARY KEY,
    companyname VARCHAR(50) 
);

-- Insert some sample data into the Company table
INSERT INTO Company (cid, companyname) VALUES
    (1, 'Google'),
    (2, 'Microsoft'),
    (3, 'Accenture'),
    (4, 'Apple'),
    (5, 'Amazon');

-- Create the Works table
CREATE TABLE Works (
    eid INT,
    cid INT,
    salary INT,
    FOREIGN KEY (eid) REFERENCES Employeee(eid),
    FOREIGN KEY (cid) REFERENCES Company(cid)
);

-- Insert some sample data into the Works table
INSERT INTO Works (eid, cid, salary) VALUES
    (1, 1, 50000),
    (2, 2, 60000),
    (3, 3, 40000),
    (4, 3, 35000),
    (5, 4, 45000);

-- Create the Manager table
CREATE TABLE Manager (
    eid INT PRIMARY KEY,
    managername VARCHAR(50),
    FOREIGN KEY (eid) REFERENCES Employeee(eid)
);

-- Insert some sample data into the Manager table
INSERT INTO Manager (eid, managername) VALUES
    (1, 'Tom Smith'),
    (2, 'John Doe'),
    (3, 'Mike Johnson');

Video :: 

This video covers all execution of the queries and sql table creation