Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
An introduction to sql querying, focusing on the describe command for table description, and various select statements for querying data. Topics include selecting specific columns, ordering results, using expressions, string manipulation, and specifying conditions. Learn how to write effective sql queries for data analysis.
Typology: Study notes
1 / 11
This page cannot be seen from the preview
Don't miss anything!
SQL> desc sales_rep Name Null? Type
SLSREP_NUMBER CHAR(2) LAST CHAR(10) FIRST CHAR(8) STREET CHAR(15) CITY CHAR(15) STATE CHAR(2) ZIP_CODE CHAR(5) TOTAL_COMMISSION NUMBER(7,2) COMMISSION_RATE NUMBER(3,2)
SQL> Select last from sales_rep; LAST
Jones Smith Diaz Martin
SQL> Select ***** from sales_rep; SL LAST FIRST STREET CITY ST ZIP_C TOTAL_COMMISSION COMMISSION_RATE
3 Jones mary 123 Main Grant MI 49219 2150. 6 Smith William 102 Raymond Ada MI 49441 4912.5. 12 Diaz Miguel 419 Harper Lansing MI 49224 2150. 18 Martin Elyse
SQL> select * from sales_rep order by Last; SL LAST FIRST STREET CITY ST ZIP_C TOTAL_COMMISSION COMMISSION_RATE
12 Diaz Miguel 419 Harper Lansing MI 49224 2150. 3 Jones mary 123 Main Grant MI 49219 2150. 18 Martin Elyse 6 Smith William 102 Raymond Ada MI 49441 4912.5.
I can rename the resulting column name as follows: SQL> select slsrep_number || ' - ' || First, Last as rep_no_n_fullname from sales_rep; SLSREP_NUMBER REP_NO_N_F
3 - mary Jones 6 - William Smith 12 - Miguel Diaz 18 - Elyse Martin
SQL> select first, Last, zip_code from sales_rep where Last = 'Diaz'; FIRST LAST ZIP_C
Miguel Diaz 49224
SQL> select first, Last, zip_code from sales_rep where Last = 'DIAZ'; no rows selected Use the Oracle function called Upper when you are not sure how the data was entered. SQL> select first, Last, zip_code from sales_rep where upper(Last) = 'DIAZ'; FIRST LAST ZIP_C
Miguel Diaz 49224
SQL> select First, last, commission_rate from sales_rep where commission_rate between 0.01 and 0.1;
William Smith. Miguel Diaz. SQL> select first, last, total_commission from sales_rep where total_commission in (2150); FIRST LAST TOTAL_COMMISSION
mary Jones 2150 Miguel Diaz 2150
SQL> select first, last, total_commission from sales_rep where total_commission not in (2150); FIRST LAST TOTAL_COMMISSION
William Smith 4912.
SQL> select First, Last, total_commission from sales_rep where Total_commission >= 2000 and commission_rate in (0.05); FIRST LAST TOTAL_COMMISSION
Miguel Diaz 2150 Using an OR produces a very different result! Make sure you understand why. SQL> select First, Last, total_commission from sales_rep where Total_commission >= 3000 or commission_rate in (0.05); FIRST LAST TOTAL_COMMISSION
William Smith 4912. Miguel Diaz 2150
8 rows selected. SQL> select distinct order_number, number_ordered from order_line; ORDER_NUMBER NUMBER_ORDERED
12491 1 12494 4 12495 2 12498 2 12498 4 12500 1 12504 2 7 rows selected.
SQL> select count () from sales_rep; COUNT()
4 If you specify a column name instead of the asterik, Oracle returns a count of rows where the column specified is not NULL. SQL> select * from sales_rep; SL LAST FIRST STREET CITY ST ZIP_C TOTAL_COMMISSION COMMISSION_RATE
3 Jones mary 123 Main Grant MI 49219 2150. 6 Smith William 102 Raymond Ada MI 49441 4912.5. 12 Diaz Miguel 419 Harper Lansing MI 49224 2150. 18 Martin Elyse SQL> select count(Street) from sales_rep; COUNT(STREET)
3
SQL> select min(Commission_rate) as minimum_CR, max(Commission_rate) as maximum_CR from sales_rep; MINIMUM_CR MAXIMUM_CR
.05. SQL> select avg(Total_commission), sum(Total_commission)from sales_rep; AVG(TOTAL_COMMISSION) SUM(TOTAL_COMMISSION)
3070.8333 9212.
SQL> select First, Last, State from sales_rep group by State, first, Last; FIRST LAST ST
Elyse Martin IA Miguel Diaz MI William Smith MI mary Jones MI SQL> select state, count() from Sales_rep group by State; ST COUNT()
IA 1 MI 3
SQL> select State, count() from sales_rep group by State having count() > 2; ST COUNT(*)
MI 3
If you forget to provide a correct join condition, you most likely will end up with the Cartesian Product. An example follows: SQL> select part_number, Customer_number from order_line 2 orders O; PART CUSTOMER_NUMBER PART CUSTOMER_NUMBER PART CUSTOMER_NUMBER
