DBMS PAPER SOLUTION

Q1.	Answer the following question in short: (any seven)	08
	(a) Definition of RDBMS.
	Answer: A Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by Dr. Edgar F. Codd. Strictly speaking it should also satisfy codd’s 12 rules, but in practice there is no DBMS that satisfies all these rules.
	(b) What is table and tuple?
	Answer: Entity information is stored in an object called Table. Multiple field s placed in a horizontal plane, is called a Record or Row or Tuple.
	(c) What is Tablespace?
	Answer: An Oracle database comprises of a number of physical files called data files. These files are logically grouped together into oracle(logical) structure called a Tablespace.
	(d) What is the difference between Truncate and delete?
	Answer: TRUNCATE Remove all records from a table, including all spaces allocated for the records are removed DELETE Deletes all records from a table, the space for the records remain
	(e) What is Data Dictionary?
	Answer: One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains: The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on) How much space has been allocated for, and is currently used by, the schema objects Default values for columns Integrity constraint information The names of Oracle users Privileges and roles each user has been granted Auditing information, such as who has accessed or updated various schema objects Other general database information
	(f) What is the difference between PRIMARY KEY and UNIQUE key?
	Answer: Primary key is unique identity and it can not insert NULL In Unique key, we can insert NULL value
	(g) The _________ constraint can only be applied at column level.
	Answer: The NOT NULL constraint can only be applied at column level
	(h) What is pseudo column?
	Answer: Pseudo means content is nothing but it is doing everything. SYSDATE is a pseudo column that contains the current date and time. It requires no arguments when selected from the table DUAL and returns the current date.
	(i) What is the difference between CHAR and VARCHAR?
	Answer: CHAR VARCHAR This datatype can hold 255 characters This datatype can hold upto 4000 characters CHAR is much faster than VARCHAR VARCHAR is less faster than CHAR The inserted values will be padded with spaces The inserted value will not be padded with spaces
	(j) What is sub query?
	Answer: A Sub Query is a form of an SQL statement that appears inside another SQL statement. It is also termed as nested query. The statement containing a sub query is called a parent statement. The parent statement is the row(i.e. the result set) returned by the subquery. Syntax: SELECT columnnames FROM tablename WHERE columnname operator (SELECT columnname FROM tablename );
	(k) What is Join statement?
	Answer: A join is a query that combines rows from two or more tables or views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. Types of JOIN: INNER JOIN OUTER ( LEFT, RIGHT, FULL) CROSS JOIN
Q2.	Answer the following question.[Any Three]	15
	(a) Explain Oracle Data type.
	Answer: Datatype is a representation of data in different type. CHAR (Size) This data type is used to store character string values of fixed length. The size in brackets determines the number of characters the cell can hold. The maximum number of characters this data type can hold is 255 characters. ORACLE compares CHAR values using blank-padded comparison semantics. E.g. if a value that is inserted in a cell of CHAR data type is shorter that the size it is defined for then it will be padded with spaces on the right until it reaches the size characters in length. VARCHAR (size) / VARCHAR2 (size) This data type is used to store variable length alphanumeric data. The maximum this data type can hold is 2000 characters. One difference between this data type and CHAR data type is VARCHAR values using non-padded comparison semantics. Inserted values will not be padded with spaces. NUMBER (P, S) The NUMBER data type is used to store numbers. The precision (P) determine the maximum length of the data, whereas the scale (S) determine the number of places to the right of the decimal. If scale is omitted then the default is zero, if the precision is omitted values are stored with their original precision up to maximum. DATE This data type is used to represent date and time. The standard format is DD-MON-YY as in 7-DEC-09. To enter dates other than the standard format use the appropriate functions. Date Time stores date in the 24-hr format. By default, the time in a date field is 12:00:00 am, if no time portion is specified. The default date for a date filed is the first day of the current month. LONG This data type is used to store variable length character strings containing up to 2GB. LONG values cannot be indexed, and the normal character functions such as SUBSTR cannot be applied to LONG values. RAW/ LONG RAW The RAW / LONG RAW data type can be used store binary data, such as digitized picture or image. Raw data type can have maximum length of 255 bytes. LONG RAW data type can contain up to 2GB Values stored in columns having LONG RAW data type cannot be indexed.
	(b) What are the range searching and pattern matching operators in oracle.
	Answer: Range Searching and Pattern Matching Operator 1) Range Searching: In order to select data that is within a range of values, the BETWEEN and NOT BETWEEN operator is used. The BETWEEN operator allows the selection of rows that contain values within a specified lower and upper limit. The range coded after the word BETWEEN is inclusive. The lower values must be coded first. The two values in between the range must be linked with the keyword AND. The BETWEEN operator can be used with both character and numeric data types. However, the data types can not be mixed. Example: The lower value of a range of values from a character column and the other from a numeric column. Synopsis: Tables: TRANS_MSTR Columns: All columns Technique: Functions: TO_CHAR(), Operators: BETWEEN, Clauses: WHERE Solution: SELECT * FROM TRANS_MSTR WHERE TO_CHAR (DT,’MM’) BETWEEN 01 AND 03; Equivalent to: SELECT * FROM TRANS_MSTR WHERE TO_CHAR (DT.’MM’) >= 01 AND TO_CHAR (DT,’MM’) <=03; Output: TRANS_NO ACCT_NO DT TYPE PARTICULAR DR CR AMT BALANCE T1 SB1 05-JAN-03 C Initial Payment D 500 500 T2 CA2 10-JAN-03 C Initial Payment D 2000 2000 T3 SB3 22-JAN-03 C Initial Payment D 500 500 T4 CA4 05-FEB-03 B Initial Payment D 2000 2000 T5 SB5 15-FEB-03 B Initial Payment D 500 500 T6 SB6 27-FEB-03 C Initial Payment D 500 500 T7 CA7 14-MAR-03 B Initial Payment D 2000 2000 T8 SB8 29-MAR-03 C Initial Payment D 500 500 8 rows selected. Explanation: The above selected will retrieve all those record from the ACCT_MSTR table where the value held in the DT field is between 01 and 03 (both values inclusive). This is doing using TO CHAR () function which Extracts the month value from the DT field. This is then compared using the AND operator. Example: Synopsis: Tables: TRANS_MSTR Columns: ACCT_NO Technique: Functions: TO_CHAR(), Operators: BETWEEN, Clauses: WHERE Solution: SELECT DISTINCT FROM TRANS_MSTR WHERE TO_CHAR (DT,’MM’) NOT BETWEEN 01 AND 04; Output: ACCT_NO ----------------- SB9 2) Pattern Matching: The use of the LIKE predicate The comparison operators discussed so far have compared one value, exactly to one other value. Such precision may not always be desired necessary. For this purpose oracle provides the LIKE predicate. The LIKE predicate allows comparison of one string value with another string value, which is not identical. This achieved by using wildcard characters. Two characters that are available: For character data types: % allows to match any string of any length (include zero length) _ allows matching on a single character Example: List the customer whose being with the letters ‘Ch’ Synopsis: Tables: CUST_MSTR Columns: FNAME,LNAME,DOB_INC Technique: Operators: BETWEEN, Clauses: WHERE, Others: ALIAS Solution: SELECT FNAME, LNAME, DOB_INC “BIRTHDATE”, OCCUP FROM CUST_MSTR WHERE FNAME LIKE ‘CH%’; Output: FNAME LNAME Birthday OCCUP Chriselle Bayross 29-OCT-82 Service Chhaya Bankar 06-OCT-76 Service Explanation: In the above examples, all those records where the values held in the FNAME beings with Ch are displayed. The % indicates that any number of characters can follow the letters Ch. The IN and NOT IN predicates: The arithmetic operator (=) compares a single value to another single value. In case a value needs to be compared to a list of values then the IN predicate is used. The IN predicates helps reduce the need to use multiple OR conditions. Example: List the customer details of the customers named Hansel, Mamta, Namita and Aruna. Synopsis: Tables: CUST_MSTR Columns: FNAME,LNAME,DOB_INC Technique: Operators: IN, Clauses: WHERE, Others: ALIAS Solution: SELECT FNAME, LNAME, DOB_INC “birthday”, OCCUP FROM CUST_MSTR WHERE FNAME IN (‘Hansel’,.Mamta’,Namita’,’Aruna’); Output: FNAME LNAME Birthday OCCUP ------------------------------------------------------------------ Mamta Muzumdar 28-AUG-75 Service Hansel Colaco 01-JAN-82 Service Namita Kanade 10-JUN-78 Self Employed Explanation: The above example, displays all those records where the FNAME filed holds any one of the four specified Values.
	(c) Explain UPDATE, GROUP BY and HAVING clause.
	Answer: Updating The Contents Of A Table: The UPDATE command is used to change or modify data values in a table. We can update all the rows from a table or a select set of rows from a table. Updating of all rows syntax is: UPDATE tablename SET columnname = expression, columnname = expression; Updating records conditionally syntax is: UPDATE tablename SET columnname = expression, columnname = expression……. WHERE columnname = expression Example: Grouping Data From Table In SQL Group By clause: The GROUP BY clause is another section of the select statement. This optional clause tells Oracle to group based on distinct values that exist for specified columns. Syntax: SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name; Having Clause: The Having Clause can be used in conjunction with the Group By clause. Having imposed a condition on the group by clause, which further filters the groups created by the group by clause? SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition); For Example: Retrieve the product no and the total quantity ordered for product‘p0001’,’p0004’ SELECT product_no, sum(qty_ordered) FROM sales_ordered GROUP BY product_no HAVING product_no=‘p0001’ OR product_no=‘p0004’;
	(d) Give the difference between DBMS and RDBMS
	Answer: DBMS RDBMS In DBMS relation between two tables or files area maintained programmatically In RDBMS relationship between two table or files can be specified at the time of table creation DBMS does not support client/server Architecture Most of the RDBMS supports client/server Architecture DBMS does not support Distributed databases Most of the RDBMS supports Distributed databases In DBMS there is no security of data In RDBMS there are multiple level of security Logging in at O/S level Command level(i.e. at RDBMS level) Object level Each table is given an extension in DBMS Many tables are grouped in one database in RDBMS DBMS may satisfy less than 7 to 8 rules of Dr. E. F. Codd RDBMS usually satisfy more than 7 to 8 rules of Dr. E.F. Codd Naming Convention Field Column, Attributes Record Row, Tuple, Entity File Table, Relation, Entity class
Q3.	Answer the following question.[Any Four]	20
	(a) List out Dr. E.F. Codd’s law and Explain First Four Rules.
	Answer: Dr. E. F. Codd’s Rules for RDBMS Dr. E. F. Codd is an IBM researcher who first developed the relational data model in 1970. In 1985, Dr. Codd published a list of 12 rules that define ideal relational database and has provided a guideline for the design of all relational database systems. Dr. Codd has used the term guideline because till date no commercial relational database system fully conforms to all 12 rules. For a few years, scorecards were kept that rated each commercial product’s conformity to Codd’s rules. Today, the rules are not talked about as much but remain a goal for relational database design. Rule 1: The Information Rule: All data should be presented in table form. Means CLIENT_MASTER table represent following: TABLE: CLIENT_MASTER COMMAND: CREATE TABLE CLIENT_MASTER ( CLIENTNO VARCHAR2(6) PRIMARY KEY, NAME VARCHAR2(20) NOT NULL, ADDRESS1 VARCHAR2(30), ADDRESS2 VARCHAR2(30), CITY VARCHAR2(15), PINCODE NUMBER(8), STATE VARCHAR2(15), BALDUE NUMBER(10,2) ); STRUCTURE: Name Null? Type ---------------------------- -------- ---- CLIENTNO NOT NULL VARCHAR2(6) NAME NOT NULL VARCHAR2(20) ADDRESS1 VARCHAR2(30) ADDRESS2 VARCHAR2(30) CITY VARCHAR2(15) PINCODE NUMBER(8) STATE VARCHAR2(15) BALDUE NUMBER(10,2) Rule 2: Guaranteed Access Rules: All data should be accessible without ambiguity. This can be accomplished through a combination of the table name, Primary Key, and column name. EXAMPLE: Emp_info Emp_Salary_info EmpId Name EmpId Salary 91 John 91 20000 92 Thomas 92 40000 OR Each and every data is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name. Rule 3: Systematic Treatment of Null Values: A field should be allowed to remain empty. This involves the support of a null value, which is distinct from an empty string or number with a value of zero. Of course, this can’t apply to primary key. In addition, most database implementations support the concept of a not- null field constraint that prevents null values in a specific table column. EXAMPLE: Emp Id Name Phone No Mobile No 09BCA01 John 2326864 9725142752 09BCA02 Thomas 2555631 - NULL Values are supported in the fully relational DBMS for representing missing information in a systematic way independent of data type. NULL values are distinct from empty character string or a string of blank character and distinct from 0 or any other number. Rule 4: Dynamic On-Line Catalog based on the Relational Model: A relational database must provide access to its structure through the same tools that are used to access the data. EXAMPLE: This is usually accomplished by storing the structure definition within special system tables. Rule 5: Comprehensive Data Sublanguage Rule: The Relational database must support at least one clearly defined language that includes functionality for data definition, data manipulation, data integrity and database transaction control. Data Definition View Definition Data Manipulation Integrate Constraints Authorization Transaction Control All commercial relational database use forms of standard SQL (i.e. Structure Query Language) as their supported comprehensive language. Rule 6: View Updating Rule: Data can be presented in different logical combination called views. Each view should support the same full range of data manipulation that has direct access to a table available. In practice, providing update and delete access to logical view is difficult and not fully supported by any current database. EXAMPLE: All views those are theoretically updatable by the system. This rule is not really implemented yet any available. Rule 7: High –Level Insert, Update, and Delete: Data can be retrieved from a relation database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table. EXAMPLE: Suppose if we need to change ID then it will reflect everywhere automatic. Rule 8: Physical Data independence: The user is isolated from the physical method of storing and retrieving information from the database. Changes can be made to the underlying architecture (hardware, disk storage methods) without affecting how the user accesses it. EXAMPLE: The user is isolated (Separated) from the physical method of storing and retrieving information from the database in which affecting directly in database. Rule 9: Logical Data Independence: How data is viewed should not be changed when the logical structure (table’s structure) of the database changes. This rule is particularly difficult to satisfy. Most databases rely on strong ties between the data viewed and the actual structure of the underlying tables. EXAMPLE: In this rule we want to retrieve any ID, when we retrieve Data without ID that time we can not satisfy this rule. Rule 10: Integrity Independence: The database language (like SQL) should support constraints on user input that maintain database integrity. This rule is not fully implemented by most major vendors. At a minimum, all databases do preserve two constraints through SQL. No component of a primary key can have a null value. If a foreign key is defined in one table, any value in it must exist as a primary key in another table. All databases do preserved to constrain through SQL. Primary Key can not have NULL. Foreign Key is define in one table any value in it must exists as a primary key in another table. Integrity constraints specific to a particular relation database must be definable in the relational data sub-language & storable in a catalog not in the application program. Rule 11: Distribution independence: A user should be totally unaware of whether or not the database is distributed (whether parts of database exist in multiple locations) make this rule difficult to implement. A variety of reasons make this rule difficult to implement. EXAMPLE: This rule difficult to implement due to variety of reasons. Rule 12: Non subversion Rule: There should be no way to modify the database structure other than through the multiple row database language (like SQL). Most databases today support administrative tools that allow some direct manipulation of the data structure. EXAMPLE: If a relational system supports a low level (Single record at a time) language that low level language can not be used to sub word or by pass the integrity rules or constraints expressed in the higher level (multiple record at a time) relational language.
	(b) What is Data Constraint? List out all type of constraint and Explain Foreign Key in detail.
	Answer: SQL Constraints: Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraint Input output constraints Business rule constraints There are two types of constraints: 1) Input output constraints. Primary key constraint Foreign key constraints Unique key constraints 2) Business rule constraints. Not null constraints Check constraints Default constraints Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table. SQL Foreign key or Referential Integrity : This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as Foreign key. Syntax : [CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name) Syntax to define a Foreign key at table level: [CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name); Example: Lets use the "product" table and "order_items". Foreign Key at column level: CREATE TABLE product ( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY, product_name char(20), supplier_name char(20), unit_price number(10) ); Foreign Key at table level: CREATE TABLE order_items ( order_id number(5) , product_id number(5), product_name char(20), supplier_name char(20), unit_price number(10) CONSTRAINT od_id_pk PRIMARY KEY(order_id), CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id) );
	(c) What is SQL *Loader? Explain in Brief.
	Answer: SQL LOADER utility is used to load data from other data source into Oracle. For example, if you have a table in FOXPRO, ACCESS or SYBASE or any other third party database, you can use SQL Loader to load the data into Oracle Tables. SQL Loader will only read the data from Flat files. So If you want to load the data from FoxPro or any other database, you have to first convert that data into Delimited Format flat file or Fixed length format flat file, and then use SQL loader to load the data into Oracle. Following is procedure to load the data from Third Party Database into Oracle using SQL Loader. Convert the Data into Flat file using third party database command. Create the Table Structure in Oracle Database using appropriate data types Write a Control File, describing how to interpret the flat file and options to load the data. Execute SQL Loader utility specifying the control file in the command line argument SQL*Loader (sqlldr ) is the utility to use for high performance data loads.  The data can be loaded from any text file and inserted into the database. Figure depicts the SQL*Loader architecture.  SQL*Loader reads a data file and a description of the data which is defined in the control file.  Using this information and any additional specified parameters (either on the command line or in the PARFILE), SQL*Loader loads the data into the database.  During processing, SQL*Loader writes messages to the log file, bad rows to the bad file, and discarded rows to the discard file.  Figure SQL*Loader Architecture  The Data Control File From SQL*Loader's perspective, the data in the data file is organized as records. A particular datafile can be in fixed record format, variable record format, or stream record format. The Control File The SQL*Loader control file contains information that describes how the data will be loaded.  It contains the table name, column datatypes, field delimiters, etc.  It simply provides the guts for all SQL*Loader processing.  Manually creating control files is an error-prone process.  The following SQL script (controlfile.sql) can be used to generate an accurate control file for a given table.  The script accepts a table name and a date format (to be used for date columns), and generates a valid control file to use with SQL*Loader for that table.    The Log File The log file contains information about the SQL*loader execution.   It should be viewed after each SQL*Loader job is complete.  Especially interesting is the summary information at the bottom of the log, including CPU time and elapsed time.  The data below is a sample of the contents of the log file. Discard file The discard file contains records that didn't meet any of the record selection criteria. The Bad File The bad file contains records rejected, either by SQL*Loader or by Oracle. parameter file Tells SQL*Loader to read command-line parameter values from a text file. This text file is referred to as a parameter fil. PARFILE (parameter file).For example, PARFILE specifies the name of a file that contains commonly used command-line parameters.
	(d) Explain Date Conversion function.
	Answer: TO_DATE: Converts a characters field to a date field. SYNTAX: TO_DATE(char [,fmt]) EXAMPLE: INSERT INTO CUST_MSTR(CUST_NO, FNAME, MNAME, LNAME, DOB_INC) VALUES(‘C1’,’Ivan’,Nelson’,’bayross’, TO_DATE(’25-JUN-1952 10:55 A.M.’,’DD-MON-YY HH:MI A.M.’)); OUTPUT: 1 rows created. 1) ADD_MONTHS: returns date after adding the number of months specified in the function. SYNTAX: ADD_MONTHS(d,n) EXAMPLE: SELECT ADD_MONTHS(SYSDATE,4) “Add Months” FROM DUAL; OUTPUT: Add Months -------------------- 01-NOV-04 2) LAST_DAY: returns the last date of the month specified with the function. SYNTAX: LAST_DAY(d) EXAMPLE: SELECT SYSDATE, LAST_DAY(SYSDATE) “LastDay” FROM DUAL; OUTPUT: SYSDATE LastDay --------------------------------- 01-JUL-04 31-JUL-04 3) MONTH_BETWEEN: Returns number of months between d1 and d2. SYNTAX: MONTHS_BETWEEN(d1, d2) EXAMPLE: SELECT MONTHS_BETWEEN(’02-FEB-92’,’02-JAN-92’) “Months” FROM DUAL; OUTPUT: Months -------------- 1 4) NEXT_DAY: Returns the date of the first weekday named bby char that is after the date named by date char must be a ady of the week. SYNTAX: NEXT_DATE(date, char) EXAMPLE: SELECT NEXT_DAY(’06-JULY-02’,’Saturday’) “NEXT DAY” FROM DUAL; OUTPUT: NEXT DAY ------------------- 13-JULY-02 5) ROUND: Returns the date rounded to a specified unit of measure. If the second parameter is omitted, the ROOUND function will round the date to the nearest day. SYNTAX: ROUND(date, char) EXAMPLE: SELECT ROUND(TO_DATE(’01-JUL-04’0,’YYYY’) “Year” FROM DUAL; OUTPUT: Year ------------------- 01-JAN-05 6) NEW_TIME: Returns the date after converting if from time zone1 to a date in time zone2. SYNTAX: NEW_TIME(date, zone1, zone2) EXAMPLE: SELECT NEW_TIME(TO_DATE(’2004/07/01 01:45’,’yyyy/mm/dd HH24:MI’),’AST’,’MST’) “MST” FROM DUAL; OUTPUT: MST ------------------- 30-JUN-04
	(e) How to delete duplicate records from the table. Explain with example.
	Answer: Delete Duplicate Records using SQL By duplicate record means that every field in one record is identical to every field in a different record, i.e. a duplicate is where there is no way of telling two or more records apart. If you just need to remove records which are similar (i.e. one or more fields are identical but there are one or more fields which are different) then instead refer to how to delete similar records. To check that you have duplicate records in your table do the following: select count(*) from MyTable and select distinct * from MyTable unfortunately SQL does not permit a select count(distinct). You have duplicate records if the number of records returned by the second query is less than the number of records returned by the first. Unfortunately there is no way in SQL to delete one of these duplicates without deleting all of them. They are identical after all, so there is no SQL query that you could put together which could distinguish between them. What you can do is to copy all the distinct records into a new table: select distinct * from MyTable This query will create a new table (NewTable in my example) containing all the records in the original table but without any records being duplicated. It will therefore preserve a single copy of those records which were duplicated. Because this query creates (and populates) a new table, it will fail if the table already exists. Delete Similar Records To delete similar records, i.e. where the records are not the same but one field is the same and only one copy needs to be preserved, try the following SQL: delete T1 from MyTable T1, MyTable T2 where T1.dupField = T2.dupField and T1.uniqueField > T2.uniqueField This will delete all records from the table MyTable which have the same value for the field dupField, leaving that record which has the lowest value in uniqueField. The above SQL works on SQL-Server and MySQL but not in MS-Access. For MS-Access try: delete from MyTable where uniqueField not in (select min(uniqueField) from MyTable T2 where T2.dupField=MyTable.dupField) This syntax also works in SQLServer (but not in MySQL) but may not be as efficient for large tables. Using Index to eliminate duplicate row It's easy to introduce duplicate rows of data into Oracle tables by running a data load twice without the primary key or unique indexes created or enabled. Here's how you remove the duplicate rows before the primary key or unique indexes can be created: DELETE FROM our_table WHERE rowid not in (SELECT MIN(rowid) FROM our_table GROUP BY column1, column2, column3... ; Here column1, column2, column3 constitute the identifying key for each record. Be sure to replace our_table with the table name from which you want to remove the duplicate rows. The GROUP BY is used on the columns that make the primary key for the table. This script deletes each row in the group after the first row.
	(f) What is DBA? Discuss the basic duties of DBA.
	Answer: “A database administrator (DBA) is a person responsible for the design, implementation, maintenance and repair of an organization's database.” Or “The DBA is a person having central control over data and processing accessing that data.” They are also known by the titles Database Coordinator or Database Programmer, and are closely related to the Database Analyst, Database Modeler. The duties of a database administrator vary depending on job description, corporate and Information Technology (IT) policies and the technical features and capabilities of the DBMS being administered. Duties of DBA Installation of new software: It is the job of the DBA to install new versions of DBMS software, application software, and other software related to DBMS administration. It is important that the DBA or other IS staff members test new software before it is moved into a production environment. Security administration: One of the main duties of the DBA is to monitor and administer DBMS security. This involves adding and removing users, administering quotas, auditing, and checking for security problems. Data analysis: The DBA analyzes data stored in the database and makes recommendations relating to performance and efficiency of that data storage. Database design: The DBA can be involved at the preliminary database-design stages, eliminating many problems that might occur. Configuration of hardware and software with the system Administrator: In many cases the system software can only be accessed by the system administrator. In this case, the DBA works closely with the system administrator to perform software installations, and to configure hardware and software so that it functions optimally with the DBMS. Data modeling and optimization: By modeling the data, it is possible to optimize the system layouts to take the most advantage of the I/O subsystem. Recoverability is one of the most important and vital duty of a Database Administrator. Any type of error such as, data entry error, hardware failure, software failure, bugs, virus etc. can result in corrupting database. Planning and implementing a sound backup and recovery strategy. DBA is responsible for create a separate tablespace within the database to hold user objects. DBA is also responsible for authorization of access data and integrity constraint specification.
Q4.	Create table: (1) Pro_mst(Pno,Pname,price,SupId)(2) Supplier(SupId,Sname,Scity) (3) Sales(SalesId,Pno,Salesdate,amount) Constraint: (1) Add foreign key SupId to Pro_mst (2) Add constraint for checking the amount greater than zero. (3) Add Primary Key constraint for SupId Solve: (1) Display the name of the supplier who supply maximum products (2) Display the list of maximum sale product	07
	Answer: CREATE TABLE Supplier ( SupId NUMBER(6) PRIMARY KEY, Sname VARCHAR(15), Scity VARCHAR(10) ); CREATE TABLE Pro_mst ( Pno NUMBER(6) PRIMARY KEY, Pname VARCHAR(15), Price NUMBER(8,2), SupId NUMBER(6) REFERENCES Supplier ); CREATE TABLE Sales ( SalesId NUMBER(6) PRIMARY KEY, Pno NUMBER(6) REFERENCES Pro_mst, Salesdate DATE, Amount NUMBER(8,2), CHECK (Amount > 0) ); Solution of Query: Display the name of the supplier who supply maximum products Answer: SELECT Supplier.Sname,max(Sales.Pno) From Supplier,Sales,Pro_mst WHERE Sales.SupId=Supplier.SupId AND Pro_mst.Pno=Sales.Pno GROUP BY Pno; Display the list of maximum sale product Answer: SELECT SalesId,sum(Pno) From Sales,Pro_mst WHERE Pro_mst.Pno=Sales.Pno GROUP BY Pno;