What is SQL?
Structured Query Language (SQL) is the set of statements with which all programs and users access data in a database.SQL lets you access and manipulate databases and SQL has become a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987
What is Oracle SQL?
Oracle SQL is an integrated development environment (IDE) for working with SQL in Oracle databases. Oracle Corporation provides this product free
Oracle also has a web platform called Oracle APEX which is a low-code development platform that enables you to build scalable, secure enterprise apps, with world-class features, that can be deployed anywhere.
Oracle APEX for SQL queries
Steps to setup Oracle APEX for SQL Queries
Click on this link https://apex.oracle.com/en/
Click on sign in
Enter your credentials or create a new account
Now after setting up your oracle account you will see this interface
Here you will get different options but for working on SQL we will use the SQL Workshop option
When you will click on SQL Workshop you will see this interface without any recent SQL commands
Now you can create objects like tables, views, etc. using Create Object option on the bottom right panel but we will use the SQL commands to create the tables and for that, you have to click on SQL Commands option
When you will click on SQL Commands option you will see this interface
This is the editor where we will right our SQL Queries
Database for practicing SQL
Now we will create some tables so that we can work on SQL
customer2 Table
Copy these lines from here into your editor and click on run
CREATE TABLE customer2
( cust_id number(11,0) not null,
cust_name varchar2(25) not null,
street varchar2(30),
city varchar2(20),
state varchar2(2),
zipcode varchar2(5),
CONSTRAINT customer_pk PRIMARY KEY (cust_id) );
ordertable2 Table
Copy these lines from here into your editor and click on run
CREATE TABLE ordertable2
( order_id number(11,0) not null,
order_date date,
cust_id number(11,0),
CONSTRAINT order_pk PRIMARY KEY (order_id),
CONSTRAINT order_fk FOREIGN KEY (cust_id) REFERENCES customer2 (cust_id));product2 Table
Copy these lines from here into your editor and click on run
CREATE TABLE product2
( product_id number(11,0) not null,
product_name varchar2(50),
product_price number(6,2),
CONSTRAINT product_pk PRIMARY KEY (product_id));
orderline2 Table
Copy these lines from here into your editor and click on run
CREATE TABLE orderline2
( order_id number(11,0) not null,
product_id number(11,0) not null,
quantity number(11,0),
CONSTRAINT orderline_pk PRIMARY KEY (order_id, product_id),
CONSTRAINT orderline_fk1 FOREIGN KEY (order_id) REFERENCES ordertable2 (order_id),
CONSTRAINT orderline_fk2 FOREIGN KEY (product_id) REFERENCES product2 (product_id));Now each time you will click on run the result panel will say Table Created
Now we will enter the data into these tables for that we will use these commands
INSERT ALL
into product2(product_id, product_name, product_price) values (101, 'Dell E5300 Laptop', 489.98)
into product2(product_id, product_name, product_price) values (102, 'Apple Laptop', 988.72)
into product2(product_id, product_name, product_price) values (103,'Printer',59)
into product2(product_id, product_name, product_price) values (104, 'Desk', 85.98)
into product2(product_id, product_name, product_price) values (105, 'Office Chair', 55.99)
into product2(product_id, product_name, product_price) values (106, 'Stapler', 15.88)
into product2(product_id, product_name, product_price) values (107, 'Index Divider', 5.99)
into product2(product_id, product_name, product_price) values (108, 'Shredder', 74.99)
into customer2(cust_id, cust_name, street, city, state, zipcode) values (1, 'John Doe', '200 Maple', 'SLC', 'UT', '84102')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (2, 'Bill Doll', '300 West', 'SLC', 'UT', '84107')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (3, 'Josh Turburn', '250 North', 'SLC', 'UT', '84108')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (4, 'Mary Lee', '200 South', 'SLC', 'UT', '84102')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (5, 'Jane Smith', '120 University', 'SLC', 'UT', '84102')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (6, 'Luis Smith', '200 Maple', 'SLC', 'UT', '84102')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (7, 'Ben Brown', '300 West', 'SLC', 'UT', '84107')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (8, 'Carl Smith', '250 North', 'SLC', 'UT', '84108')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (9, 'John Doll', '200 South', 'SLC', 'UT', '84102')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (10, 'Jennet Chris', '120 University', 'SLC', 'UT', '84102')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (11, 'Joyce French', '200 Main', 'Tucson', 'AZ', '45102')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (12, 'Jennifer English', '250 State', 'Tucson', 'AZ', '45112')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (13, 'Tom Borg', '3000 Sunset', 'LA', 'CA', '12112')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (14, 'Helen Thomas', '1200 Hollywood', 'LA', 'CA', '12117')
into customer2(cust_id, cust_name, street, city, state, zipcode) values (15, 'Brian Borg', '100 College', 'SF', 'CA', '17118')
into ordertable2(order_id, order_date, cust_id) values (1001, '10-24-2008', 1)
into ordertable2(order_id, order_date, cust_id) values (1002, '10-21-2008', 8)
into ordertable2(order_id, order_date, cust_id) values (1003, '10-22-2008', 15)
into ordertable2(order_id, order_date, cust_id) values (1004, '10-22-2008', 5)
into ordertable2(order_id, order_date, cust_id) values (1005, '10-24-2008', 3)
into ordertable2(order_id, order_date, cust_id) values (1006, '10-24-2008', 2)
into ordertable2(order_id, order_date, cust_id) values (1007, '10-27-2008', 11)
into ordertable2(order_id, order_date, cust_id) values (1008, '10-30-2008', 12)
into ordertable2(order_id, order_date, cust_id) values (1009, '11-1-2008', 4)
into ordertable2(order_id, order_date, cust_id) values (1010, '11-5-2008', 1)
into orderline2(order_id, product_id, quantity) values (1001, 101, 2)
into orderline2(order_id, product_id, quantity) values (1001, 102, 2)
into orderline2(order_id, product_id, quantity) values (1001, 104, 1)
into orderline2(order_id, product_id, quantity) values (1002, 103, 5)
into orderline2(order_id, product_id, quantity) values (1003, 103, 3)
into orderline2(order_id, product_id, quantity) values (1004, 106, 2)
into orderline2(order_id, product_id, quantity) values (1004, 108, 2)
into orderline2(order_id, product_id, quantity) values (1005, 104, 4)
into orderline2(order_id, product_id, quantity) values (1006, 104, 1)
into orderline2(order_id, product_id, quantity) values (1006, 105, 2)
into orderline2(order_id, product_id, quantity) values (1006, 107, 2)
into orderline2(order_id, product_id, quantity) values (1007, 102, 2)
into orderline2(order_id, product_id, quantity) values (1008, 103, 3)
into orderline2(order_id, product_id, quantity) values (1008, 108, 3)
into orderline2(order_id, product_id, quantity) values (1009, 104, 2)
into orderline2(order_id, product_id, quantity) values (1009, 107, 3)
into orderline2(order_id, product_id, quantity) values (1010, 108, 10)
SELECT * FROM dual;Now the result panel will say 50 row(s) inserted.
Congratulations you have successfully created the database now you can use these questions for practicing
SQL Questions for practice
List IDs of products that have been ordered. One ID appears exact one time. Order product IDs in ascending order.
List IDs of customers that have placed ordered after 27-OCT-2008. One ID appears exactly one time. Order customer IDs in ascending order.
List all customers who are from SLC and whose firstexactly name starts with the letter ‘J’.
List product name, product price, and product price after a 10% discount.
List the number of products with prices higher than 100. (The answer is 2)
List name and price for all products that have been purchased in order 1001. Using IN to implement this query.
Using conditional JOIN (ON) to implement query 6.
Using natural join to implement query 6.
List total quantity ordered for each order
List total dollar amount ordered for each order, where for each product ordered its amount equals to quantity times price.
If you want a full solution or you want to learn SQL or anything then please send help request at contact@codersarts.com or fill the form or you can Chat with our website assistance
תגובות