Database for Lab test(mySQL)

Database ရဲ့ 2012 က practical test ေမးခြန္းေလးပါ...
Lab test အတြက္အဆင္ေၿပေအာင္ ဒီေမးခြန္းေလးကို အဆင့္လိုက္ၿပန္ေၿဖေပးထားပါတယ္...
#Question#
(1)Create the following tables which contain the following associated fields.
               Suppliers (sid CHAR(5),sname CHAR(20),address CHAR(20))
               Parts (pid CHAR(5),pname CHAR(20),color(10))
               Catalog (sid CHAR(5),pid CHAR(5),cost NUMBER)
(2)Fill the following data into tables.
              Suppliers 
                     Sid  Sname  address
                     S1 Esther London
                     S2 James Paris
                     S3 John    London
                     S4 Nigel Rome
                     S5 Smith Athens
                     S6 Jones Paris
                     S7 Clark Rome



            parts
                     Pid  Pname  Color
                     P1   Bolt          Green
                     P2   Nut           Red
                     P3   Cam   Blue
                     P4   Cog           Red
                     P5   Screw   Blue
                     P6   Glass   Grey


           Catalog

                    Sid         Pid       Cost

                    S2          P1       100
                    S1          P1       100
                    S3          P2       300
                    S6          P3       200
                    S2          P2       700
                    S4          P6       900
                    S5          P4       500
                    S2          P3       150

 Write SQL statement for the following problem using the tables create in (1).
(3)Add a new supplier s8 into supplier table. The sname and address is Blake and London.
(4)Change the color of all blue parts to orange.
(5)Get full details of all parts.
(6)Get full details of all suppliers in London.
(7)Get address of supplier who supplies part p6.
(8)Get all detail of shipments where the cost is in the range 300 to 500.
(9)Find the names of suppliers who supply grey part of green part.

__________________________________________________________________________
__________________________________________________________________________
database တည္ေဆာက္ပံု အဆင့္ဆင့္နွင့္ Question ေၿဖရွင္းခ်က္မ်ားကို ပံုႏွင့္တကြ ေဖာ္ၿပေပးထားပါတယ္.

(1)create database & tables

  mysql>create database 3cs10000;

  mysql>use 3cs10000;

  mysql> create table suppliers(sid char(5),sname char(20),address char(20),primary                         key(sid));
  
  mysql> create table parts(pid char(5),pname char(20),color char(10),primary key(
             pid));

  mysql> create table catalog(sid char(5),pid char(5),cost integer,primary key(sid
            ,pid),foreign key(sid) references suppliers(sid),foreign key(pid) references par
             ts(pid));


3cs10000 ေန၇ာမွာ ကိုယ္ေဆာက္ခ်င္တဲ့ database ၇ဲ့ နာမည္ေ၇းတာပါ use 3cs10000 ဆိုတာကေတာ့ ဒီ 3cs10000 ဆို တဲ့ database ကို သံုးမယ္လို့ ေၿပာလိုက္တာပါ။ ဒါဆိုဆက္ပီး tables create နိုင္ပါပီ.
table သံုးခုကို ေတာ့ sql create query အတိုင္းေ၇းပီး အထက္ပါအတိုင္းေဆာက္ပါ.
အဓိက အခ်က္ကေတာ့ table တိုင္းမွာ primary key ပါ၇မွာၿဖစ္ပီးေတာ့ အခ်င္းခ်င္း ခ်ိတ္ဆက္ထားတဲ့ table မွာဆိုရင္ေတာ့ primary key အၿပင္ foreign key ပါပါရပါတယ္.

(2)Fill data into tables.

mysql> insert into suppliers values('S1','Esther','London'),('S2','James','Paris
            '),('S3','John','London'),('S4','Nigel','Rome'),('S5','Smith','Athens'),('S6','J
                ones','Paris'),('S7','Clark','Rome');

   mysql> insert into parts values('P1','Bolt','Green'),('P2','Nut','Red'),('P3','C
               am','Blue'),('P4','Cog','Red'),('P5','Screw','Blue'),('P6','Glass','Gery');

   mysql> insert into catalog values('S2','P1','100'),('S1','P1','100'),('S3','P2',
                '300'),('S6','P3','200'),('S2','P2','700'),('S4','P6','900'),('S5','P3','500'),(
                'S2','P3','150');

ေမးခြန္းမွာ ေပးတဲ့ အတိုင္း data ေတြကိုေတာ့ insert query နဲ့ အထက္ပါအတိုင္းထည့္ပါတယ္.
ဒါဆို၇င္ေတာ့ data မ်ား table ေတြထဲကို ၀င္သြားမွာပါ .. 
ပီး၇င္ေတာ့ ေမးခြန္းေတြဆက္ေၿဖလို့ရပါပီ.

(3)Add a new supplier s8 into supplier table. The sname and address is Blake and London.

    mysql> insert into suppliers values('S8','Blake','London');

ေၿပာင္လဲသြားတဲ့ table ကိုၿမင္ေအာင္လို့ select * နဲ့ ၿပန္ေခၚၿပထားတာပါ အသစ္၀င္လာတဲ့ data ကို ၿမင္ေစခ်င္လို့ပါ.

(4)Change the color of all blue parts to orange.

(5)Get full details of all parts.

mysql> update parts set color='Orange' where color='Blue';

mysql> select * from parts; 

(6)Get full details of all suppliers in London.

 mysql> select * from suppliers where address='London';

(7)Get address of supplier who supplies part p6.

 mysql> select address
        -> from suppliers
        -> where sid=
        ->      (select sid
        ->       from catalog
        ->       where  pid='P6');

(8)Get all detail of shipments where the cost is in the range 300 to 500.

 mysql> select * from catalog where cost>300 and cost<500;

empty set လို့ၿပပါတယ္ ..၃၀၀ နဲ့ ၅၀၀ ၾကားမွာ လည္း တကယ္မရွိပါဘူးး catalog table မွာၾကည့္နိုင္ပါတယ္.

(9)Find the names of suppliers who supply grey part of green part.

 mysql> select sname
        -> from suppliers
        -> where sid In
        ->       (select sid
        ->        from catalog
        ->        where pid In
        ->          (select pid
        ->           from parts
        ->           where color='Grey' or color='Green'));

အထက္ပါေ၇းနည္းကေတာ့ subsequence နဲ့ေ၇းတာပါ mySQL မွာ ဒီေ၇းနည္းက ပို error ကင္းလို့ပါ။..
..
..
အဆင္ေၿပပါေစေနာ္ 
304 Lab test ကို အမွတ္ၿပည့္ေၿဖနိုင္ပါေစ.. :D