Write SQL statement for the following: (any 5)
a) Supplier (Sup-no, sup-name, city, states).
b) Part (part-no, part-name, weight, color, price).
c) Shipment (sup-no, part-no, qty).
sup-no and part-no are primary keys.
i) Find the entire supplier who supplies “SCREW”.
ii) Display the total no. of suppliers from each city.
iii) Display the supplier name, part name with quantity more than 100.
iv) Delete the supplier from city “Pune” and status is 10.
v) Change the status for Mumbai as 20.
vi) Find the total weight and total price for all parts.
Create the three tables as given below.
Supplier (Sup-no, sup-name, city, states).
Part (part - no, part-name, weight, color, price)
Shipment (sup-no, part-no, qty)
i) Find the entire supplier who supplies “SCREW”.
Select S.Name, SH.PartNo, P.PartName from Supplier S innerjoin Shipment SH on S.[No] = SH.SupNo Innerjoin Part P on P.PartNo = SH.PartNo and P.PartName = 'Screw'
ii) Display the total no. of suppliers from each city.
Select COUNT(name) TotalSupplier, City from Supplier groupby City
iii) Display the supplier name, part name with quantity more than 100.
Select S.Name,P.PartName from Supplier S innerjoin Shipment SH on S.[No] = SH.SupNo Innerjoin Part P on P.PartNo = SH.PartNo and SH.Qty >100
iv) Delete the supplier from city “Pune” and status is 10.
update Supplier set [Status] = 10 where City ='Pune'
v)Change the status for Mumbai as 20.
update Supplier set [Status] = 20 where City = 'Mumbai'
vi) Find the total weight and total price for all parts.
Select SUM(P.weight) TotalWeight, SUM(P.Price) TotalPrice from Part P groupby PartNo
Write short notes on E.F. Codd's rules.
These rules can be applied on any database system that supports relational capabilities.
Rule 1 : Information rule
Data in a database must be stored in a table format.
Rule 2 : Guaranteed Access
All data that resides in the database must be accessible with a combination of table-name, primary-key (row value), and attribute-name (column value).
Rule 3 : Systemetic treatment of NULL
NULL has several meanings, it can mean missing data, not applicable or data is not known value. So it must be treated uniformly and systematic. Primary key must not be null.
Rule 4 : Active Online Catalog
The description of the entire database must be stored in an online catalog, which is called as data dictionary. Online catalog is accessed by authorized users. The same query language should be used on catalog as used on database.
Rule 5 : Powerful language
For accessing database a powerful language should be there, that having linear syntax that supports data definition, data manipulation, and transaction management operations. If database users are able to access data without any help of this particular language, then it should be considered as a violation of this rule.
Rule 6 : View Updation rule
All the views of a database, which can theoretically be updated, must also be updatable by the system.
Rule 7 : Relational Level Operation
Database must support Relational Level Operation, means if you have performed insert, update, delete or other operation then these operations must reflect at each level of relations. Set operation like Union, Intersection and minus should also be supported.
Rule 8 : Physical Data Independence
The data stored in a database must be independent of the applications that access the database. If you are doing any change in physical structure of a database it must not reflect or create problem that how the data is being accessed by external applications.
Rule 9 : Logical Data Independence
The logical data in a database must be independent of its user’s view. Changes to the data tables, columns, rows, procedures etc, must not require a change to an application based on the structure.
Rule 10 : Integrity Independence
Key constraints, Check constraints, trigger etc. should be stored in Data Dictionary. All these integrity constraints can be independently changeable without the need of any change in the application.
Rule 11 : Distribution Independence
Data is distributed over various locations and end-user must not be able to see these locations. Database data should be Distribution Independence. Only authenticated users can see the required data.
Rule 12 : Non-subversion rule
If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to disrupt the system,
for example, you cannot ignore a relational security or integrity constraint.
