Que2.31. Consider the following relation. The primary key is Rollno, ISBN, Student( Roll No, Name, Branch), Book( ISBN, Title, Author, Publisher) Issue( Roll No, ISBN,date_of_issue). Write the query in relational algebra and SQL of the following List the Roll Number and Name of All CSE Branch Students. Relational Data Model & Language 2 – 38 A( CS/ IT- Sem- 5) ii. Find the name of scholars who have issued a book of publication ‘ BPB ’. iii. List the title and author of all books which are issued by a pupil name started with ‘ a ’. iv. List the title of all books issued on or before20/09/2012. List the name of pupil who’ll read the book of author named ‘ Sanjeev ’. AKTU 2017- 18, Marks 10 Answer i. In relational algebra Roll No, Name( Branch = “ CSE ”( Student)) In SQL Select Roll No, Name from scholars where Branch = “ CSE ”; ii. In relational algebra Name Publisher = “ BPB ” andStudent_Roll No = P.Roll No( Student( Roll No, Publisher(Issue.ISBN = Book.ISBN P( Book Issue)))) In SQL SelectStudent.name from Pupil inner join electBook.Publisher,Issue.Roll No from Issue inner join Book on = Book.ISBN as P) ONStudent.Roll No = P. Roll No whereP.Publisher = “ BPB ”; iii. In relational algebra ,S.AuthorS.Name like( ‘ a ’)(T.Name,Book.Author,Book.TitleBook.ISBN = T.ISBN S( Book( Name, ISBNStudent.Roll No = Issue.Roll No T( Student Issue)))); In SQL SelectS.title,S.Author from electT.Name,Book.Author,Book.Title from Book inner join( elect ,Issue.ISBN from Pupil inner join Issue ONStudent.Roll No = Issue.Roll No as T) ONBook.ISBN = T.ISBN as S) whereS.Name like ‘ a ’; iv. In relational algebra Title( date> = 20/09/2012( Book Issue)) Database Management System 2 – 39 A( CS/ IT- Sem- 5) In SQL SelectBook.Title from Book inner join Issue ONBook.ISBN = Issue.ISBN as R whereR.date> = 20/09/2012; iv. In relational algebra Name( Author = “ Sanjeev ” andStudent.Roll No = Q.Roll No( Student Roll No, Author = Book.ISBN Q( Book Issue))) In SQL SelectStudent.Name from Pupil inner join electIssue.Roll No,Book.Author from Issue inner join Book ON = Book.ISBN as Q) ONStudent.Roll No = Q.Roll No whereQ.Author = “ Sanjeev ”; Que2.32. Suppose there are two relations R( A, B, C), S( D, E, F). Write TRC and SQL for the following RAs A, B( R) ii. B = 45( R) iii. A, F( C = D( R × S)) Answer A, B( R) TRC{s.A,s.B| R( s)} SQL elect A, B from R; ii. B = 45( R) TRC{ s| R( s)s.B = 45} SQL elect * from R where B = 45; iii. A, F( C = D( R × S)) TRC{ t| p r q s( t( A) = p( A) t( F) = q( F) p( C) = q( D))} SQL elect A, F from R inner join S ONR.C = S.D; Que2.33. Consider the following relational DATABASE. Give an expression in SQL for each following queries. Accentuate records are primary crucial Hand(person_name, road, megacity) workshop(person_name,Company_name, payment) Company(Company_name, megacity) Manages(person_name,manager_name) Finds the names of all workers who works for the ABC bank. ii. Finds the name of all workers who live in the same megacity and on the same road as do their directors. iii. Find the name road address and metropolises of hearthstone of all workers who work for ABC bank and earn further than,000 per annum. iv. Find the name of all hand who earn further than every hand of XYZ. Give all workers of pot ABC a 7 payment rise. vi. cancel all tuples in the workshop relation for workers of ABC. vii. Find the name of all workers in this DATABASE who live in the same megacity as the company for which they work. AKTU 2019- 20, Marks 07 Answer Selectperson_name from Works Wherecompany_name = ‘ ABC Bank ’ ii. electE1.person_name From Hand as E1, Hand as E2, Manages as M WhereE1.person_name = M.person_name andE2.person_name = M.manager_name andE1.street = E2.street andE1.city = E2.city iii. elect * from hand whereperson_name in electperson_name from Works wherecompany_name = ‘ ABC Bank ’ and payment> 7000 selectE.person_name, road, megacity from Hand as E, Works as W whereE.person_name = W.person_name andW.company_name = ‘ ABC Bank ’ andW.salary> 7000 iv. electperson_name from Works where payment> all elect payment from Works wherecompany_name = ‘ XYZ ’) selectperson_name from Works where payment>( elect maximum( payment) from Works wherecompany_name = ‘ XYZ ’) Update Works set payment = payment *1.07 wherecompany_name = ‘ ABC Bank ’ vi. cancel from Works Database Management System 2 – 41 A( CS/ IT- Sem- 5) wherecompany_name = ‘ ABC Bank ’ vii. SelectE.person_name from Hand as E, Works as W, Company as C whereE.person_name = W.person_name andE.city = C.city andW.company_name = C.company_name Que2.34. Explain bedded SQL and dynamic SQL in detail. Answer Bedded SQL 1. The SQL standard defines embeddings of SQL in a variety of programming languages similar as Pascal, PL I, Fortran, C and COBOL. 2. A language in which SQL queries are bedded is appertained to as a host language and the SQL structures permitted in the host language constitute bedded SQL. 3. Programs written in the host language can use the bedded SQL syntax to pierce and modernize data stored in a database. 4. In bedded SQL, all query processing is performed by the database system. 5. The result of the query is also made available to the program one tuple at a time. 6. Bedded SQL statements must be fully present at collect time and collected by the bedded SQL preprocessor. 7. To identify bedded SQL requests to the preprocessor, we use the superintendent, SQL statement as superintendent SQLEND.EXEC 8. Variable of the host language can be used within bedded SQL statements, but they must be anteceded by a colon() to distinguish them from SQL variables. Dynamic SQL 1. The dynamic SQL element of SQL allows programs to construct and submit SQL queries at run time. 2. Using dynamic SQL, programs can produce SQL queries as strings at run time and can either have them executed incontinently or have them set for posterior use. 3. Preparing a dynamic SQL statement compiles it, and posterior uses of the set statement use the collected interpretation. 4. SQL defines norms for bedding dynamic SQL calls in a host language, similar as C, as in the following illustration, housekeeper * sqlprog = “ update account set balance = balance *1.05 whereaccount_number = ? ”; superintendent SQL prepare dynprog from sqlprog; housekeeper account( 10) = “A-101 ”; superintendent SQL execute dynprog using account; Que2.35. Describe procedures in PL/ SQL with its advantages and disadvantages. Answer 1. PL/ SQL is a block- structured language that enables inventors to combine the power of SQL with procedural statements. 2. A stored procedure in PL/ SQL is nothing but a series of declarative SQL statements which can be stored in the database roster. 3. A procedure can be allowed
of as a function or a system. 4. They can be invoked through triggers, other procedures, or operations on Java, PHPetc. 5. All the statements of a block are passed to Oracle machine each at formerly which increases processing speed and decreases the business. Advantages of procedures in PL/ SQL 1. They affect in performance enhancement of the operation. If a procedure is being called constantly in an operation in a single connection, also the collected interpretation of the procedure is delivered. 2. They reduce the business between the database and the operation, since the lengthy statements are formerly fed into the database and need not be transferred again and again via the operation. 3. They add to decode reusability, analogous to how functions and styles work in other languages similar as C/ C and Java. Disadvantages of procedures in PL/ SQL 1. Stored procedures can beget a lot of memory operation. The database director should decide an upper bound as to how numerous stored procedures are doable for a particular operation. 2. MySQL doesn’t give the functionality of remedying the stored procedures.
