Database Queries, Views, and Correlated Nested Queries Assignment

This assignment provides a comprehensive set of SQL queries designed to interact with a database. It includes queries for selecting data, joining tables, grouping results, and using aggregate functions. The assignment also covers creating and utilizing database views to simplify complex queries and improve data access. Furthermore, it delves into correlated nested queries, demonstrating how to filter data based on subquery results. The solution demonstrates how to create views, access them, and determine their updatability. The assignment covers a range of SQL concepts, including selecting data, joining tables, grouping results, using aggregate functions, creating views, and employing correlated nested queries to solve a variety of database-related tasks.

1.SELECT fName,lName FROM conductor order by fname;
2. SELECT concert.title, concert.date, concert.ticketPrice, conductor.fname
FROM concert inner join conductor on
concert.conductorEmployeeId=conductor.conductorEmployeeId;
3. SELECT consertMusician.instrumentId, instrument.instrumentName from
consertMusician inner join instrument on
consertMusician.instrumentId=instrument.instrumentId group by
consertMusician.instrumentId order by count(consertMusician.instrumentId)
desc limit 1;
4. SELECT fName, lName FROM conductor WHERE
conductorEmployeeId NOT IN (SELECT musicianEmployeeId FROM
consertMusician);
5. SELECT conductor.fName, conductor.lName FROM concert inner join
consertMusician on
concert.conductorEmployeeId=consertMusician.musicianEmployeeId and
concert.concertId=consertMusician.consertId inner join conductor on
concert.conductorEmployeeId=conductor.conductorEmployeeId;
6. SELECT instrument.instrumentName, count(employee.employeeID) as
countEmployee from employee inner join instrument on
employee.instrumentId=instrument.instrumentId group by
employee.instrumentId;
7, CREATE VIEW allData AS SELECT AVG(concert.ticketPrice) as
avgTicketPrice, COUNT(consertMusician.musicianEmployeeId) as
countConcert, musician.fName, musician.lName FROM consertMusician
inner join concert on consertMusician.consertId=concert.concertId inner
join musician on
consertMusician.musicianEmployeeId=musician.musicianEmployeeId
GROUP BY consertMusician.musicianEmployeeId HAVING countConcert
> 1;

8. CREATE VIEW allTitle AS SELECT musician.fName,
musician.lName, concert.title from musician left join consertMusician on
musician.musicianEmployeeId=consertMusician.musicianEmployeeId left
join concert on concert.concertId=consertMusician.consertId;
9. No views that we create in the query 7 and 8 are not updatable because
in that we write the query to create a view we can access it and make it
updatable by the following command -
To access view -
SELECT * FROM allData;
To updatable view -
SELECT allData, is_updatable FROM
information_schema.views where
table_schema=’classicmodels’;
10. Example of correlated nested query
SELECT * FROM concert where concertId in (select
concertId from concert where ticketPrice > 80);