Que2.10. Write short note on SQL. Explain colorful characteristics of SQL. Answer 1. SQL stands for Structured Query Language. 2. It’s anon-procedural language that can be used for reclamation and operation of data stored in relational database. 3. It can be used for defining the structure of data, modifying data in the database and specifying the security constraints. 4. The two major orders of SQL commands are Data Definition Language( DDL) DDL provides commands that can be used to produce, modify and cancel database objects. Data Manipulation Language( DML) DML provides commands that can be used to pierce and manipulate the data, that is, to recoup, fit , cancel and modernize data in a database. Characteristics of SQL 1. SQL operation is extremely flexible. 2. It uses a free form syntax that gives the stoner the capability to structure SQL statements in a way stylish suited to them. 3. Each SQL request is parsed by the RDBMS before prosecution, to check for proper syntax and to optimize the request. 4. Unlike certain programming languages, there’s no need to start SQL statements in a particular column or be finished in a single line. The same SQL request can be written in a variety of ways. Que2.11. What are the advantages and disadvantages of SQL? Answer Advantages of SQL 1. Faster query processing Large quantum of data is recaptured snappily and efficiently. Operations like insertion, omission, manipulation of data is done in nearly no time. 2. No coding chops For data reclamation, large number of lines of law is not needed. All introductory keywords similar as SELECT, INSERT INTO, UPDATE, etc are used and also the syntactical rules aren’t complex in SQL, which makes it a stoner-friendly language. 3. Standardised language Due to attestation it provides a invariant platform worldwide to all its druggies. 4. movable It can be used in programs in PCs, garçon, laptops independent of any platform( Operating System, etc). Also, it can be bedded with other operations as per need/ demand/ use. 5. Interactive language Easy to learn and understand, answers to complex queries can be entered in seconds. Disadvantages of SQL 1. Complex interface SQL has a delicate interface that makes many druggies uncomfortable while dealing with the database. 2. Cost Some performances are expensive and hence, programmers can not pierce it. 3. Partial control Due to retired business rules, complete control is not given to the database. Que2.12. What are the different datatypes used in SQL? Answer SQL supports following datatypes 1. housekeeper( n) A fixed length character string with stoner specified maximum lengthn. 2. varchar( n) A variable length character string with stoner specified maximum lengthn. 3. int An integer which is a finite subset of the integers that’s machine dependent. 4. small int A small integer is machine independent subset of integer sphere type. 5. numeric( p, d) A fixed point number with stoner defined perfection. It consists of p integers and d of the p integers are to the right of the decimal point. 6. real or double perfection Floating point and double perfection floating point figures with machine dependent perfection. 7. pier( n) A floating point number with perfection of at least n integers. 8. date A timetable date containing a time( four number), month( two number) and day( two number) of the month. 9. time The time of the day in hours, twinkles and seconds. Que2.13. What are the types of nonfictional used in SQL? Answer The four kinds of nonfictional values supported in SQL are 1. Character string Character strings are written as a sequence of characters enclosed in single quotations. b. The single quotation character is represented within a character string by two single quotations. For illustration, ‘ Computer Engg ’, ‘ Structured Query Language ’ 2. Bit string a. A bit string is written either as a sequence of 0s and 1s enclosed in single quotations and anteceded by the letter ‘ B ’ or as a sequence of hexadecimal integers enclosed in single quotations and anteceded by the letter ‘ X ’. b. For illustration, B ’ 1011011 ’, B ’ 1 ’, B ’ 0 ’, X’A 5 ’, X’T ’ 3. Exact numeric a. These literals are written as a inked or unsigned decimal number conceivably with a decimal point. b. For illustration, 9, 90,90.00,0.9,99.99, –99.99. 4. Approximate numeric Approximate numeric literals are written as exact numeric literals followed by the letter ‘ E ’, followed by a inked or unsigned integer. b. For illustration, 5E5,55.5 E5, 55E – 5, 55E – 5, 055E, –5.55 E – 9. Que2.14. What are the different types of SQL commands? Answer Different types of SQL commands are 1. fit a. This command is used to fit tuples in a table. b. This command adds a single tuple at a time in a table. Syntax fit intotable_name( attribute1,., attributen) values(values_list); 2. Update a. This command is used to make changes in the values of attributes of the table. b. It use set and where clause. Syntax Updatetable_name setattribute_name = new_value where condition; 3. cancel a. This command is used to remove tuples. Tuples can be deleted from only one table at a time. Syntax cancel fromtable_name where condition; 4. elect This command is used to recoup a subset of tuples from one or further table. Syntax Select attribute1,., attributen fromtable_name where condition; 5. Alter table a. This command is used to make changes in the structure of a table. b. This command is used to add an trait ii. to drop an trait iii. to brand an trait iv. to add and drop a constraint Syntax Alter tabletable_name addcolumn_name datatype; Alter tabletable_name drop columncolumn_name; Alter tabletable_name drop constraintconstraint_name; Que2.15. Write a short note on SQL DDL commands. Answer SQL DDL is used to define relation of a system. The general syntax of SQL judgment is VERB( parameter1, parameter2;., parametern) b. The relations are created using CREATE verb. 1. produce TABLE This command is used to produce a new relation and the corresponding syntax is CREATE TABLErelation_name field1 datatype( size), field2 datatype( size),., fieldn datatype size)); 2. produce TABLE. AS SELECT. This type of produce command is used to produce the structure of a new table from the structure of being table. The generalized syntax of this form is CREATE TABLErelation_name1 field1, field2,., fieldn) AS SELECT field1, field2,., fieldn FROMrelation_name2; Structure of relations are changed using ALTER verb. 1. ALTER TABLE. ADD. This is used to add some redundant columns into an being table. The generalized format is ALTER TABLErelation_name ADD( new field1 datatype( size), new field2 datatype( size),., new fieldn datatype( size)); 2. ALTER TABLE. MODIFY. This form is used to change the range as well as data type of being relations. The generalized syntax is ALTER TABLErelation_name MODIFY( field1 new data type( size), field2 new data type( size), —————- fieldn new data type( size)); Que2.16. Draw an ER illustration of Hospital or Bank with showing the specialization, Aggregation, conception. Also convert it in to relational schemas and SQL DDL. AKTU 2017- 18, Marks 10 Database Management System 2 – 17 A( CS/ IT- Sem- 5) Answer Relational schemas branch( branch- name, branch- megacity, means) client( client- name, client- road, client- megacity, client- id) account( account- number, balance) loan( loan- number, quantum) hand( hand- id, hand- name, telephone- number, start- date, employment length, dependent- name) payment( payment- number, payment- quantum, payment- date) saving- account( interest- rate) checking- account( overdraft- quantum) Que2.19. Give the brief explanation of view. Answer 1. A view is a virtual relation, whose contents are deduced from formerly being relations and it doesn’t live in physical form. 2. The contents of view are determined by executing a query grounded on any relation and it doesn’t form the part of database schema. 3. Each time a view is appertained to, its contents are deduced from the relations on which it’s grounded. 4. A view can be used like any other relation that is, it can be queried, fitted into, deleted from and joined with other relations or views. 5. Views can be grounded on further than one relation and similar views are known as complex views. 6. A view in SQL language is a single table that’s deduced from other tables. These other tables can be base tables or preliminarily defined views. Syntax for creating view CREATE VIEWview_name AS SELECT * FROMtable_name WHERE Category IN( ‘ attribute1 ’, ‘ attribute2 ’); For illustration Command to produce a view conforming of attributes , Category, Price andP_ID of the BOOK relation, Pname and State of the PUBLISHER relation can be specified as produce VIEWBOOK_3 AS SELECTBOOK_title, Category, Price,BOOK.P_ID, Pname, State FROM BOOK, PUBLISHER WHEREBOOK.P_ID = PUBLISHER.P_ID;
