Enhance your database table structures, via your selected RDBMS, as you wish...

Question

Enhance your database table structures, via your selected RDBMS, as you wish...

Then, using SQL and an SQL script file, add at least five more rows of data to each of your tables.

Then, using SQL and an SQL script file, create and execute advanced queries of your choice that demonstrate each of the following:

The use of a GROUP BY clause, with a HAVING clause, and one or more group functions

The use of UNION operator

The use of a subquery

The use of an outer join

Then create and execute at least one SQL UPDATE and at least one SQL DELETE query.

Finally, create and use an SQL view and in a SELECT query.

Submit the following outputs of your SQL script file processing, in this order and appropriately labeled, in a single Word file:

The SQL and results of your INSERT statements to further populate your tables

The SQL and results of your 4 advanced SELECT queries

The SQL and results of your UPDATE and DELETE queries

The SQL and results of your view creation and its use in a SELECT query

You must show all of your SQL as it executed in Management Studio or other development environments. This includes the results returned by your selected RDBMS.

At the top of your Word file include your name, and the date.

Explanation / Answer

Please use sql server management studo only. out of the chegg rules to provided name

select * from Employee

--create table
create table Department
(
DeptId int identity(1,1) primary key,
DeptName nvarchar(50)
)

--inset rows in table
insert into Department(DeptName) values('IT')
insert into Department(DeptName) values('HR')
insert into Department(DeptName) values('Payrol')
insert into Department(DeptName) values('VFX')

--select table
select * from Department

--Update column in table
update Department set Company='WIPRO' where DeptId = 4

--select table
select * from Department

--delete row from table department
delete from Department where DeptId = 4

--select table
select * from Department

--Gropu By and Having
select Department,sum(Salary) from Employee
group by Department
having SUM(Salary) > 50000

--SubQuery
--second max salary
select max(Salary) from Employee
where Salary not in (select max(Salary) from Employee)

--or

select top(1) salary from
(select top(2) salary from Employee order by salary desc) t order by salary asc

--Join
select e.EmpId, e.EmpName, e.Salary, e.Department, d.company
from Employee e
inner join Department d
on e.Department = d.DeptName

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.