top of page

Oracle SQL Query Help

Updated: Nov 29, 2021

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

  1. Click on this link https://apex.oracle.com/en/

  2. Click on sign in

  3. 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

  1. List IDs of products that have been ordered. One ID appears exact one time. Order product IDs in ascending order.

  2. List IDs of customers that have placed ordered after 27-OCT-2008. One ID appears exactly one time. Order customer IDs in ascending order.

  3. List all customers who are from SLC and whose firstexactly name starts with the letter ‘J’.

  4. List product name, product price, and product price after a 10% discount.

  5. List the number of products with prices higher than 100. (The answer is 2)

  6. List name and price for all products that have been purchased in order 1001. Using IN to implement this query.

  7. Using conditional JOIN (ON) to implement query 6.

  8. Using natural join to implement query 6.

  9. List total quantity ordered for each order

  10. 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


Comentarios


bottom of page