|
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:
|
|
|
|
(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:
|
|
|
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.
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)
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
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.
This is usually accomplished by storing the structure definition
within special system tables.
Data Definition
View Definition
Data
Manipulation
Integrate
Constraints
Authorization
Transaction
Control
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.
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.
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.
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.
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.
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.
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.
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;
|
|
