Que2.20. Describe indicators in SQL. Answer 1. indicators are special lookup tables that the database hunt machine can use to speed up data reclamation. 2. An indicator helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. 3. indicators can be created or dropped with no effect on the data. 4. indicators are used to recoup data from the database more snappily. 5. The druggies can not see the indicators; they’re just used to speed up quests queries. 6. Syntax CREATE indicator indicator ON TABLE column; where the indicator is the name given to that indicator and TABLE is the name of the table on which that indicator is created and column is the name of that column for which it’s applied. 7. Unique indicators are used for the conservation of the integrity of the data present in the table as well as for the fast performance; it does not allow multiple values to enter into the table. Syntax for creating unique indicator is CREATE UNIQUE indicatorindex_name ON TABLE column; 8. To remove an indicator from the data wordbook in using the DROP INDEX command. DROP indicatorindex_name; Que2.21. Explainsub-query with illustration. Answer 1. Asub-query is a SQL query nested inside a larger query. 2. Sub-queries must be enclosed within gap. 3. Thesub-query can be used with the SELECT, INSERT, UPDATE, or cancel statement along with the drivers like = ,>,<,> = ,< = , IN, ANY, ALL, BETWEEN. 4 Asub-query is generally added within the WHERE clause of another SQL SELECT statement. 5. Asub-query is also called an inner query while the statement containing asub-query is also called an external query. 6. The inner query executes first before its parent query so that the result of an inner query can be passed to the external query. Syntax of SQLsub-query Asub-query with the IN driver, SELECTcolumn_names FROMtable_name1 WHEREcolumn_name IN( electcolumn_name FROMtable_name2 WHERE condition); illustration We’ve the following two tables ‘ pupil ’ and ‘ marks ’ with common field ‘ StudentID ’. Now considering table ‘ Student ’, we want to write a query to identify all scholars who get further marks than the pupil whose StudentID is ‘ V002 ’, but we don’t know the marks of ‘ V002 ’. So, consider another table ‘ Marks ’ containing total marks of the pupil and apply query considering both tables. SQL law withsub-query SELECTa.StudentID,a.Name,b.Total_marks FROM pupil a, marks b WHEREa.StudentID = b.StudentID ANDb.Total_marks> electTotal_marks FROM marks WHERE StudentID = ‘ V002 ’); Que2.24. Explain how a database is modified in SQL. OR Explain database revision. Answer Different operations that modify the contents of the database are 1. cancel a. The delete operation is used to cancel all or specific rows from database. cancel command don’t cancel values of particular attributes. c. A delete command operates only on relation or table. Syntax cancel fromtable_name where condition; 2. fit Insert command is used to fit data into a relation/ table. b. The trait values for fitted tuples must be members of the trait’s sphere specified in the same order as in the relation schema. Syntax Insert intotable_name values( attribute1, attribute2, attribute3, attributeN); 3. Updates Update command is used to modernize a value in a tuple. Syntax Updatetable_name setcolumn_name = new_value condition; Que2.28. Explain cursors, sequences and procedures used in SQL. Answer Cursors 1. A cursor is a temporary work area created in the system memory when a SQL statement is executed. 2. A cursor contains information on a select statement and the rows of data penetrated by it. 3. A cursor can hold further than one row, but can reuse only one row at a time. 4. The set of rows the cursor holds is called the active set. 5. There are two types of cursors Implicit cursors i. These are created by dereliction when DML statements like, INSERT, UPDATE, and cancel statements are executed. ii. They’re also created when a SELECT statement that returns just one row is executed. unequivocal cursors i. They must be created when we’re executing a SELECT statement that returns further than one row. ii. When we cost a row the current row position moves to coming row. Sequences Sequences are constantly used in databases because numerous operations bear each row in a table to contain a unique value and sequences give an easy way to induce them. Syntax CREATE SEQUENCE( schema.)sequence_name AS datatype) START WITH value) proliferation in value) MINVALUE value| NO MINVALUE) MAXVALUE value| NO MAXVALUE) CYCLE| NO CYCLE) CACHE value| NO CACHE); Procedures 1. A procedure is asub-program that performs a specification. 2. A procedure has two corridor Specification The procedure specification begins with the keyword procedure and ends with the procedure name or parameter list. ii. Body The procedure body begins with the keyword is and ends with the keyword end. Syntax To produce a procedure, produce or replace procedure( parameter list) is original protestation> begin executable statements) exception)( exception instructors) end; Syntax To execute a procedure, superintendent( parameters); Que2.29. What’s detector? Explain different detector with illustration. OR Describe the following terms detector Answer Triggers 1. A detector is a procedure( law member) that’s executed automatically when some specific events do in a table/ view of a database. 2. Alarms are substantially used for maintaining integrity in a database. Alarms are also used for administering business rules, auditing changes in the database and replicating data. Following are different types of triggers 1. Data Manipulation Language( DML) triggers DML triggers are executed when a DML operation like INSERT, UPDATE OR cancel is fired on a Table or View. DML triggers are of two types i. AFTER triggers 1. AFTER triggers are executed after the DML statement completes but before it’s committed to the database. 2. AFTER triggers if needed can rollback its conduct and source DML statement which invoked it. ii. rather OF triggers 1. rather OF triggers are the triggers which get executed automatically in place of driving DML( i.e., INSERT, UPDATE and cancel) action. 2. It means if we’re fitting a record and we’ve a rather OF detector for INSERT also rather of INSERT whatever action is defined in the detector that gets executed. 2. Data Definition Language( DDL) triggers DDL triggers are executed when a DDL statements like CREATE, ALTER, DROP, GRANT, DENY, drop, and UPDATE STATISTICS statements are executed. DDL triggers can be DATABASE scoped or Garçon scoped. The DDL triggers with garçon position compass gets fired in response to a DDL statement with garçon compass like CREATE DATABASE, produce LOGIN,GRANT_SERVER, ALTER DATABASE, ALTER LOGINetc. Where as DATABASE scoped DDL triggers fire in response to DDL statement with DATABASE compass like CREATE TABLE, produce PROCEDURE, CREATE FUNCTION, ALTER TABLE, ALTER PROCEDURE, ALTER FUNCTIONetc. 3. LOGON triggers LOGON triggers get executed automatically in response to a LOGON event. b. They get executed only after the successful authentication but before the stoner session is established. c. If authentication fails the LOGON triggers won’t be fired. 4. CLR triggers CLR triggers are grounded on the Sql CLR. b. We can write DML and DDL triggers by using the supported. NET CLR languages likeC#,VB.NETetc. CLR triggers are useful if heavy calculation is needed in the detector or a reference to object outside SQL is needed. Que2.30. Consider the following relational database hand employee_name, road, megacity workshop(employee_name, , payment) company(company_name, megacity) manage employee_name,manager_name). Give an expression in SQL to express each of the following queries Find the names and metropolises of hearthstone of all workers who work for XYZ bank. ii. Find the names, road address, and metropolises of hearthstone of all hand who works for XYZ Bank and earn further than ,000 per annum. iii. Find the names of all workers in this database who live in the same megacity as the company for which they work. Answer SelectE.employee_name, megacity from hand E, works W whereW.company_name = ‘ XYZ Bank ’ and = E.employee_name ii. elect * from hand whereemployee_name in selectemployee_name from Works wherecompany_name = ‘ XYZ Bank ’ and payment> 10000 selectE.employee_name, road address, megacity from Hand as E, Works as W whereE.employee_name = W.person_name and = ‘ XYZ Bank ’ andW.salary> 10000 iii. SelectE. hand, name from Hand as E, Works as W, Company as C whereE. hand, name = W.person_name andE.city = C.city andW.company_name = C.company_name
