SQL Facts SQL stands for Structured Query Language SQL is pronounced sequel SQL is declarative language SQL is used to access & manipulate data in databases Top SQL DBs are MS SQL Server, Oracle, DB2, and MySQL SQL Commands Categories Data Query Language (DQL) SELECT. A detailed SQL cheat sheet with essential references for keywords, data types, operators, functions, indexes, keys, and lots more. For beginners and beyond. ORACLE SQL REFERENCE CARD WWW.EM-GROUP-LLC.COM. Reserved Words Built-in Functions Common Clauses/Misc Cmds ALTER / DROP COMMANDS CREATE Commands. Jan 29, 2020 Oracle uses @ for running scripts. You can check out more here. You can use comments to include text that won’t get evaluated as part of your sql query. The standard set of comments tokens are –, /.,./, and MySQL additionally uses # for inline comments.

  1. Pl Sql Cheat Sheet Pdf
  2. Oracle Sql Functions Cheat Sheet
  3. Oracle Sql Cheat Sheet Pdf
  4. Sql Cheat Sheet
  • Categories: cheat-sheet , cli , database
  • #oracle , #sql , #sqlplus
  • 5 minutes read

In this post, I’m going to aggregate all those Oracle commands that I can neverremember but are very useful to have somewhere written down.

Table of Contents

Intro

Last week, I suddenly had to work with an Oracle database again. Inormally use Intellij’s DataGripto connect to databases. I tried it this time, and I found I could notconnect to the schema I wanted: the schema just turned up empty. Ofcourse, everybody will recommend you use Oracle’s SQL Developer with anyOracle database you have to touch. So, after trying brew search sqldeveloper (yes, I’m on a Mac at work), coming up empty, readingthis caskrequest andfeeling the anticipation of endless frustration grow inside me, I wentto Oracle’s web site to see if I could download the program. I can,except that they want me to turn in a DNA sample first:

Of course, faking those kind of details is not impossible, but thehassle of going through something like that just for a lousy program soI can just run a couple of lousy queries puts me off. Luckily, I had aDocker container with Oracle XE lying around. It includes SQL*Plus, the“venerable” command-line tool provided by Oracle to query and administerOracle databases. Being a Arch Linux user with a predilection foranything with a command-line interface, it was not too bad, but gettingthe output formatted so that I could actually make sense of it requiredmore effort than most users are willing to put up with. So how do youfind your way around when you have SQL*Plus and no clue how it works?How do you find the schema’s you want, the tables you need to query? Howcan you check your privileges? This post aims to be a cheat sheet so youcan find your way around. Keep in mind that the results you’re gettingdepend on the privileges your user has. Getting an empty result doesnot mean that the database object does not exist. Rather, it meansyou’re a lesser god.

Getting Help With sqlplus

sqlplus does not have a man page, but provides help when you pass-h/-help:

Connecting to an Oracle Database Using SQL*Plus

The basic syntax to connect as user alice with password qwerty to adatabase FOO which is located on db.domain.tld and listens on port1521 (default port) is:

Show the Connected User

The SHOW command lets you look at the current state of your SQL*Plusenvironment: SHOW ALL shows all settings.

Show All Schema’s

Return only non-empty schema’s (excluding most users who never createdany object in the database):

Excluding Oracle’s built-in schema’s:

Show All Tables/Views in a Particular Schema

Related: find all views:

Describe a Table

Show DDL of a Table

where the first argument is the type of object (e.g. 'TABLE','VIEW', 'TRIGGER'), the second is the name of the object, and thethird the schema where the object is defined.

Utc local time converter

Show the Privileges of Your User

Get More Decent Output From a Command

If you want SQL*Plus to truncate the value of a column:

otherwise it will allow the column to wrap to the next line (defaultON). Suppress all headings, page breaks, titles, the initial blankline and other formatting information:

SourceNow, if only Oracle would support G for vertical output…

Get and Alter Database Timezones

Select DATE Rows in the Last Hour

Table t has a column c_date of type DATE:

This works because you can subtract a fraction from a DATE type wherethe fraction is interpreted as a fraction of a day (1 is an entireday, 0.5 is 12 hours, etc.).

Cheat

Working Around Oracle Not Having a LIMIT

Yes, Oracle does not have a LIMIT keyword, so this idiom will quicklybecome ingrained in your muscle memory:

For each row returned by a query, the ROWNUM pseudocolumn returns anumber indicating the order in which Oracle selects the row from atable or set of joined rows. The first row selected has a ROWNUM of1, the second has 2, and so on. — Oracledocumentation

You could do without the subquery, plainly writing:

but if you add a ORDER BY clause, Oracle will first select the firstten rows, and only then apply the ORDER BYclause, which might not be what you want. So that’s why it’s best toalways use the first idiom above.

Setting New Password for User

Show Output from Script

When writing a script, you may want to output some diagnostic data:

You think you’re good to go, but when you execute your script in an environmentwhere my.table does not exist, you don’t see the diagnostic message. Whatgives? SQL*Plus’s default behavior is to suppress output by default. You haveto SET SERVEROUTPUT ON first.

Download this 2-page SQL Basics Cheat Sheet in PDF or PNG format, print it out, and stick to your desk.

The SQL Basics Cheat Sheet provides you with the syntax of all basics clauses, shows you how to write different conditions, and has examples. You can download this cheat sheet as follows:

You may also read the contents here:

SQL Basics Cheat Sheet

SQL

SQL, or Structured Query Language, is a language to talk to databases. It allows you to select specific data and to build complex reports. Today, SQL is a universal language of data. It is used in practically all technologies that process data.

SAMPLE DATA

QUERYING SINGLE TABLE

Fetch all columns from the country table:

Fetch id and name columns from the city table:

Fetch city names sorted by the rating column in the default ASCending order:

Fetch city names sorted by the rating column in the DESCending order:

Aliases

Columns

Tables

FILTERING THE OUTPUT

COMPARISON OPERATORS

Fetch names of cities that have a rating above 3:Fetch names of cities that are neither Berlin nor Madrid:

TEXT OPERATORS

Fetch names of cities that start with a 'P' or end with an 's':Fetch names of cities that start with any letter followed by'ublin' (like Dublin in Ireland or Lublin in Poland):

OTHER OPERATORS

Fetch names of cities that have a population between 500K and 5M:Fetch names of cities that don't miss a rating value:Fetch names of cities that are in countries with IDs 1, 4, 7, or 8:

QUERYING MULTIPLE TABLES

INNER JOIN

JOIN (or explicitly INNER JOIN) returns rows that have matching values in both tables.

LEFT JOIN

LEFT JOIN returns all rows from the left table with corresponding rows from the right table. If there's no matching row, NULLs are returned as values from the second table.

RIGHT JOIN

RIGHT JOIN returns all rows from the right table with corresponding rows from the left table. If there's no matching row, NULLs are returned as values from the left table.

FULL JOIN

FULL JOIN (or explicitly FULL OUTER JOIN) returns all rows from both tables – if there's no matching row in the second table, NULLs are returned.

CROSS JOIN

CROSS JOIN returns all possible combinations of rows from both tables. There are two syntaxes available.

NATURAL JOIN

NATURAL JOIN will join tables by all columns with the same name.

NATURAL JOIN used these columns to match rows:
city.id, city.name, country.id, country.name.
NATURAL JOIN is very rarely used in practice.

AGGREGATION AND GROUPING

GROUP BYgroups together rows that have the same values in specified columns. It computes summaries (aggregates) for each unique combination of values.

AGGREGATE FUNCTIONS

  • avg(expr) − average value for rows within the group
  • count(expr) − count of values for rows within the group
  • max(expr) − maximum value within the group
  • min(expr) − minimum value within the group
  • sum(expr) − sum of values within the group

EXAMPLE QUERIES

Find out the number of cities:

Find out the number of cities with non-null ratings:

Find out the number of distinctive country values:

Find out the smallest and the greatest country populations:

Find out the total population of cities in respective countries:

Find out the average rating for cities in respective countries if the average is above 3.0:

SUBQUERIES

A subquery is a query that is nested inside another query, or inside another subquery. There are different types of subqueries.

SINGLE VALUE

The simplest subquery returns exactly one column and exactly one row. It can be used with comparison operators =, <, <=, >, or >=.

This query finds cities with the same rating as Paris:

MULTIPLE VALUES

A subquery can also return multiple columns or multiple rows. Such subqueries can be used with operators IN, EXISTS, ALL, or ANY.

This query finds cities in countries that have a population above 20M:

CORRELATED

A correlated subquery refers to the tables introduced in the outer query. A correlated subquery depends on the outer query. It cannot be run independently from the outer query.

This query finds cities with a population greater than the average population in the country:

This query finds countries that have at least one city:

Pl Sql Cheat Sheet Pdf

SET OPERATIONS

Set operations are used to combine the results of two or more queries into a single result. The combined queries must return the same number of columns and compatible data types. The names of the corresponding columns can be different

UNION

UNION combines the results of two result sets and removes duplicates. UNION ALL doesn't remove duplicate rows.

This query displays German cyclists together with German skaters:

INTERSECT

INTERSECT returns only rows that appear in both result sets.

This query displays German cyclists who are also German skaters at the same time:

Oracle Sql Functions Cheat Sheet

EXCEPT

EXCEPT returns only the rows that appear in the first result set but do not appear in the second result set.

This query displays German cyclists unless they are also German skaters at the same time:

Oracle Sql Cheat Sheet Pdf

Try out the interactive SQL Basics course at LearnSQL.com, and check out our other SQL courses.

Sql Cheat Sheet

You may also like