API tools faq
paste
Advertisement
Guest User

Iliyan & Stefan Kursova Oracle2

a guest
Dec 12th, 2018
87
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Oracle 11 7.65 KB | None | 0 0
raw download clone embed print report
  1. -- Generated by Oracle SQL Developer Data Modeler 4.1.5.907
  2. -- at: 2018-12-06 09:21:49 EET
  3. -- site: Oracle Database 11g
  4. -- type: Oracle Database 11g
  5. CREATE TABLE Automobile ( Id INTEGER NOT NULL , Brand_Id INTEGER NOT NULL , Model_Id INTEGER , YEAR DATE , Color_Id INTEGER , Kilometers INTEGER , Price NUMBER ) ;
  6. ALTER TABLE Automobile ADD CONSTRAINT Automobile_PK PRIMARY KEY ( Id ) ;
  7. CREATE TABLE Brand ( Id INTEGER NOT NULL , Name VARCHAR2 ( 32 ) ) ;
  8. ALTER TABLE Brand ADD CONSTRAINT Brands_PK PRIMARY KEY ( Id ) ;
  9. CREATE TABLE Client ( Id INTEGER NOT NULL , Name VARCHAR2 ( 25 ) , Address VARCHAR2 ( 50 ) , Telephone VARCHAR2 ( 13 ) ) ;
  10. ALTER TABLE Client ADD CONSTRAINT Client_PK PRIMARY KEY ( Id ) ;
  11. CREATE TABLE Color ( Id INTEGER NOT NULL , Name VARCHAR2 ( 32 ) ) ;
  12. ALTER TABLE Color ADD CONSTRAINT Color_PK PRIMARY KEY ( Id ) ;
  13. CREATE TABLE Employee ( Id INTEGER NOT NULL , Name VARCHAR2 ( 25 ) , Position_Id INTEGER , Telephone VARCHAR2 ( 13 ) ) ;
  14. ALTER TABLE Employee ADD CONSTRAINT Employee_PK PRIMARY KEY ( Id ) ;
  15. CREATE TABLE Model ( Id INTEGER NOT NULL , Name VARCHAR2 ( 32 ) ) ;
  16. ALTER TABLE Model ADD CONSTRAINT Model_PK PRIMARY KEY ( Id ) ;
  17. CREATE TABLE Position ( Id INTEGER NOT NULL , Name VARCHAR2 ( 25 ) ) ;
  18. ALTER TABLE Position ADD CONSTRAINT Position_PK PRIMARY KEY ( Id ) ;
  19. CREATE TABLE Sales ( Id INTEGER NOT NULL , Employee_Id INTEGER , Client_Id INTEGER , Automobile_Id INTEGER , "Date" DATE ) ;
  20. ALTER TABLE Sales ADD CONSTRAINT Sales_PK PRIMARY KEY ( Id ) ;
  21. ALTER TABLE Automobile ADD CONSTRAINT Automobile_Brand_FK FOREIGN KEY ( Brand_Id ) REFERENCES Brand ( Id ) ;
  22. ALTER TABLE Automobile ADD CONSTRAINT Automobile_Color_FK FOREIGN KEY ( Color_Id ) REFERENCES Color ( Id ) ;
  23. ALTER TABLE Automobile ADD CONSTRAINT Automobile_Model_FK FOREIGN KEY ( Model_Id ) REFERENCES Model ( Id ) ;
  24. ALTER TABLE Sales ADD CONSTRAINT Client_Id FOREIGN KEY ( Client_Id ) REFERENCES Client ( Id ) ;
  25. ALTER TABLE Sales ADD CONSTRAINT Employee_Id FOREIGN KEY ( Employee_Id ) REFERENCES Employee ( Id ) ;
  26. ALTER TABLE Employee ADD CONSTRAINT Employee_Position_FK FOREIGN KEY ( Position_Id ) REFERENCES Position ( Id ) ;
  27. ALTER TABLE Sales ADD CONSTRAINT automobile_id FOREIGN KEY ( Automobile_Id ) REFERENCES Automobile ( Id ) ;
  28. CREATE SEQUENCE Automobile_Id_SEQ START WITH 1 NOCACHE ORDER ;
  29. CREATE OR REPLACE TRIGGER Automobile_Id_TRG BEFORE INSERT ON Automobile FOR EACH ROW WHEN ( NEW . Id IS NULL ) BEGIN : NEW . Id := Automobile_Id_SEQ . NEXTVAL ;
  30. END ; /
  31. CREATE SEQUENCE Brand_Id_SEQ START WITH 1 NOCACHE ORDER ;
  32. CREATE OR REPLACE TRIGGER Brand_Id_TRG BEFORE INSERT ON Brand FOR EACH ROW WHEN ( NEW . Id IS NULL ) BEGIN : NEW . Id := Brand_Id_SEQ . NEXTVAL ;
  33. END ; /
  34. CREATE SEQUENCE Client_Id_SEQ START WITH 1 NOCACHE ORDER ;
  35. CREATE OR REPLACE TRIGGER Client_Id_TRG BEFORE INSERT ON Client FOR EACH ROW WHEN ( NEW . Id IS NULL ) BEGIN : NEW . Id := Client_Id_SEQ . NEXTVAL ;
  36. END ; /
  37. CREATE SEQUENCE Color_Id_SEQ START WITH 1 NOCACHE ORDER ;
  38. CREATE OR REPLACE TRIGGER Color_Id_TRG BEFORE INSERT ON Color FOR EACH ROW WHEN ( NEW . Id IS NULL ) BEGIN : NEW . Id := Color_Id_SEQ . NEXTVAL ;
  39. END ; /
  40. CREATE SEQUENCE Employee_Id_SEQ START WITH 1 NOCACHE ORDER ;
  41. CREATE OR REPLACE TRIGGER Employee_Id_TRG BEFORE INSERT ON Employee FOR EACH ROW WHEN ( NEW . Id IS NULL ) BEGIN : NEW . Id := Employee_Id_SEQ . NEXTVAL ;
  42. END ; /
  43. CREATE SEQUENCE Model_Id_SEQ START WITH 1 NOCACHE ORDER ;
  44. CREATE OR REPLACE TRIGGER Model_Id_TRG BEFORE INSERT ON Model FOR EACH ROW WHEN ( NEW . Id IS NULL ) BEGIN : NEW . Id := Model_Id_SEQ . NEXTVAL ;
  45. END ; /
  46. CREATE SEQUENCE Position_Id_SEQ START WITH 1 NOCACHE ORDER ;
  47. CREATE OR REPLACE TRIGGER Position_Id_TRG BEFORE INSERT ON Position FOR EACH ROW WHEN ( NEW . Id IS NULL ) BEGIN : NEW . Id := Position_Id_SEQ . NEXTVAL ;
  48. END ; /
  49. CREATE SEQUENCE Sales_Id_SEQ START WITH 1 NOCACHE ORDER ;
  50. CREATE OR REPLACE TRIGGER Sales_Id_TRG BEFORE INSERT ON Sales FOR EACH ROW WHEN ( NEW . Id IS NULL ) BEGIN : NEW . Id := Sales_Id_SEQ . NEXTVAL ;
  51. END ; /
  52. INSERT INTO BRAND ( ID , NAME ) VALUES ( 1 , 'Audi' );
  53. INSERT INTO BRAND ( ID , NAME ) VALUES ( 2 , 'BMW' );
  54. INSERT INTO BRAND ( ID , NAME ) VALUES ( 3 , 'Mitsubishi' );
  55. INSERT INTO BRAND ( ID , NAME ) VALUES ( 4 , 'Subaru' );
  56. INSERT INTO MODEL ( ID , NAME ) VALUES ( 1 , 'A5' );
  57. INSERT INTO MODEL ( ID , NAME ) VALUES ( 2 , 'X5' );
  58. INSERT INTO MODEL ( ID , NAME ) VALUES ( 3 , 'Eclipse' );
  59. INSERT INTO MODEL ( ID , NAME ) VALUES ( 4 , 'WRX STI' );
  60. INSERT INTO CLIENT ( ID , NAME , ADDRESS , TELEPHONE ) VALUES ( 1 , 'Mitko Dinev' , 'Vuzrajdane' , '123456789' );
  61. INSERT INTO CLIENT ( ID , NAME , ADDRESS , TELEPHONE ) VALUES ( 2 , 'Danny Ivanova' , 'Mladost' , '333333333' );
  62. INSERT INTO CLIENT ( ID , NAME , ADDRESS , TELEPHONE ) VALUES ( 3 , 'Stf Kolev' , 'JP Gara' , '444444444' );
  63. INSERT INTO CLIENT ( ID , NAME , ADDRESS , TELEPHONE ) VALUES ( 4 , 'Marto Iliev' , 'Vuzrajdane' , '555555555' );
  64. INSERT INTO COLOR ( ID , NAME ) VALUES ( 1 , 'blue' ); INSERT INTO COLOR ( ID , NAME ) VALUES ( 2 , 'green' );
  65. INSERT INTO COLOR ( ID , NAME ) VALUES ( 3 , 'red' ); INSERT INTO COLOR ( ID , NAME ) VALUES ( 4 , 'grey' );
  66. INSERT INTO COLOR ( ID , NAME ) VALUES ( 5 , 'black' ); INSERT INTO EMPLOYEE ( ID , NAME , POSITION_ID , TELEPHONE ) VALUES ( 1 , 'Strahil' , 2 , '555555555' );
  67. INSERT INTO EMPLOYEE ( ID , NAME , POSITION_ID , TELEPHONE ) VALUES ( 2 , 'Ivailo' , 3 , '666666666' ); INSERT INTO EMPLOYEE ( ID , NAME , POSITION_ID , TELEPHONE ) VALUES ( 3 , 'Chocho' , 1 , '777777777' );
  68. INSERT INTO AUTOMOBILE ( ID , BRAND_ID , MODEL_ID , YEAR , COLOR_ID , KILOMETERS , PRICE ) VALUES ( 1 , 1 , 1 , TO_DATE ( '01-MAY-08' , 'DD-MON-RR' ), 4 , 140000 , 14000 );
  69. INSERT INTO AUTOMOBILE ( ID , BRAND_ID , MODEL_ID , YEAR , COLOR_ID , KILOMETERS , PRICE ) VALUES ( 2 , 4 , 4 , TO_DATE ( '01-JUL-03' , 'DD-MON-RR' ), 4 , 183743 , 9800 );
  70. INSERT INTO AUTOMOBILE ( ID , BRAND_ID , MODEL_ID , YEAR , COLOR_ID , KILOMETERS , PRICE ) VALUES ( 3 , 3 , 3 , TO_DATE ( '01-OCT-98' , 'DD-MON-RR' ), 4 , 213000 , 4500 );
  71. INSERT INTO SALES ( ID , EMPLOYEE_ID , CLIENT_ID , AUTOMOBILE_ID , "Date" ) VALUES ( 1 , 1 , 3 , 2 , TO_DATE ( '08-NOV-18' , 'DD-MON-RR' )); UPDATE AUTOMOBILE SET color_id = 1 WHERE brand_id = 3 ;
  72. SELECT * FROM AUTOMOBILE LEFT JOIN BRAND ON BRAND . ID = AUTOMOBILE . BRAND_ID WHERE BRAND . NAME = '&brand' ORDER BY BRAND_ID ;
  73. SELECT * FROM AUTOMOBILE LEFT JOIN MODEL ON MODEL . ID = MODEL . BRAND_ID WHERE MODEL . NAME = '&model' ORDER BY MODEL_ID ;
  74. SELECT * FROM AUTOMOBILE LEFT JOIN COLOR ON COLOR . ID = AUTOMOBILE . COLOR_ID WHERE COLOR . NAME = '&color' ORDER BY COLOR_ID ;
  75. /* Example input: 20-Apr-03(from date) and 12-Jan-13(to date) */
  76. SELECT * FROM AUTOMOBILE WHERE YEAR BETWEEN TO_DATE ( '&fromdate' , 'DD-MON-RR' ) AND TO_DATE ( '&todate' , 'DD-MON-RR' ) ORDER BY YEAR ;
  77. SELECT * FROM AUTOMOBILE WHERE kilometers LIKE '&kilometers' ORDER BY kilometers ;
  78. SELECT * FROM AUTOMOBILE WHERE price LIKE '&price' ORDER BY price ;
  79. SELECT * FROM SALES LEFT JOIN AUTOMOBILE ON AUTOMOBILE . ID = SALES . AUTOMOBILE_ID RIGHT JOIN EMPLOYEE ON EMPLOYEE . ID = SALES . EMPLOYEE_ID WHERE EMPLOYEE . NAME = '&name' ORDER BY SALES . "Date" ;
  80. SELECT * FROM ( SELECT * FROM SALES LEFT JOIN AUTOMOBILE ON AUTOMOBILE . ID = SALES . AUTOMOBILE_ID ORDER BY AUTOMOBILE . PRICE ) WHERE ROWNUM <= 5 ;
  81. SELECT * FROM SALES LEFT JOIN CLIENT ON CLIENT . ID = SALES . CLIENT_ID RIGHT JOIN AUTOMOBILE ON AUTOMOBILE . ID = SALES . AUTOMOBILE_ID WHERE CLIENT . NAME = '&name' ORDER BY CLIENT . NAME ;
  82. /* Example input: 20-Apr-03(from date) and 12-Jan-13(to date) */
  83. SELECT * FROM SALES WHERE SALES . "Date" BETWEEN TO_DATE ( '&fromdate' , 'DD-MON-RR' ) AND TO_DATE ( '&todate' , 'DD-MON-RR' ) ORDER BY SALES . "Date" ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!