MySQL

Sample Queries

select book_code, title from book;
select * from branch;
select publisher_name, city from publisher where city=’Boston’;
select publisher_name, city from publisher where city != ‘new york’;
select branch_name, num_employees from branch where num_employees >= 9;
select book_code, title, type from book where type=’hor’;
select book_code, title, type, paperback from book where type=’hor’ and paperback=’y’;
select book_code, title, type, publisher_code from book where type=’hor’ or publisher_code=’sc’;
select book_code, title, price from book where price >= 15 and price<=25;
select book_code, title, type, price from book where price<20 and type=’mys’;

create view MAJOR_CUSTOMER as select customer_num, customer_name, balance, credit_limit, rep_num from customer where balance<=10000;
select customer_num, customer_name from major_customer where balance>credit_limit;

create view PART_ORDER as select p.part_num, description, price, ol.order_num, order_date, num_ordered, quoted_price from part p, order_line ol, orders o where p.part_num=ol.part_num and ol.order_num=o.order_num;
select part_num, description, order_num, quoted_price from part_order where quoted_price>100;

create view ORDER_TOTAL (order_num, order_total) as select order_num,num_ordered*quoted_price from order_line order by order_num;
select order_num, order_total from order_total where order_total>1000;

grant select on part to ashton;
grant insert on orders, order_line to kelly, morgan;
grant update (price) on part to james;
grant delete on customer to danielson;
grant select (customer_num, customer_name, street, city, state, zip) on customer to public;
grant index on orders to perez;
grant alter on part to washington;
grant all on orders to grinstead;
create view plume as select book_code, title, type, price from book where publisher_code like ‘PL’;
select book_code, title, price from plume where price<13;

create index PART_INDEX1 on order_line(part_num);
create index PART_INDEX2 on part(class);
create index PART_INDEX3 on part(class,warehouse);
create index PART_INDEX4 on part(class desc, warehouse);

drop index PART_INDEX3 on part;

select table_name from systables where column_name like ‘customer_num’;
select column_name, data_type from syscolumns where table_name like ‘part’;

alter table order_line add foreign key (order_num) references orders;

alter table customer add check (credit_limit in (5000, 7500, 10000, 15000));