Sqlplus Cheat Sheet

Posted on by



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

Title: SQL Cheatsheet Author: SQL-Tutorial.net Keywords: SQL Cheatsheet;Learn SQL;SQL Tutorial Created Date: 4/14/2011 9:37:02 PM. This is a cheat sheet for Oracle 12C R2 workshop Part I Will conver those topics: EM CBD PDB Network User Security Tablespaces. Sqlplus / as sysdba. SQL Cheat Sheet for SQLPlus, MSSQL etc. GitHub Gist: instantly share code, notes, and snippets. Oracle Cheat Sqlplus Commands d2nvmy6529nk. Home (current) Explore. Aix Commands Cheat Sheet.doc November 2019 23. Vim Commands Cheat Sheet.pdf.

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.

Sheet

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.

Sqlplus Cheat Sheet Excel

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

Oracle Sql Cheat Sheet

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

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.

This SQL tutorial helps you get started with SQL quickly and effectively through many practical examples.

If you are a software developer, database administrator, data analyst, or data scientist who wants to use SQL to analyze data, this tutorial is a great start.

Each topic is covered clearly and concisely with many practical examples so that you can both truly understand the concept and know how to apply it to solve the data problems more effectively.

SQL stands for Structured Query Language designed to manipulate data in the Relational Database Management Systems (RDBMS). Today, SQL is one of the most common programming languages for interacting with data.

Section 1: Introduction to SQL

  • What is SQL – give you a brief overview of the SQL language and its popular dialects.
  • SQL Syntax – provide you with the syntax of the SQL language.
  • SQL Sample Database – introduce you to an HR sample database.

Section 2: Querying Data

  • SELECT Statement – show you how to query data from a single table by using the simplest form of the SELECT statement.

Sqlplus Cheat Sheets

Section 3: Sorting Data

  • ORDER BY Clause – sort the data by one or more columns in the ascending and/or descending order.

Section 4: Filtering Data

  • DISTINCT – show you how to remove duplicates from the result set.
  • LIMIT – constrain a number of rows returned by a query using the LIMIT and OFFSET clause.
  • FETCH – learn how to skip N rows in a result set before starting to return any rows.
  • WHERE Clause – filter data based on specified conditions.
  • Comparison operators – learn how to use the comparison operators including greater than, greater than or equal, less than, less than or equal, equal, and not equal to form the condition in the WHERE clause.
  • Logical operators – introduce the logical operators and how to use them to test for the truth of a condition.
  • AND operator – combine multiple Boolean expressions using the AND logical operator.
  • OR operator – show you how to use another logical operator OR to combine multiple Boolean expressions.
  • BETWEEN Operator – guide you to use the BETWEEN operator to select data within a range of values.
  • IN Operator – show you how to use the IN operator to check whether a value is in the list of values.
  • LIKE Operator – query data based on a specified pattern.
  • IS NULL Operator – introduce the NULL concepts and show you how to check whether an expression is NULL or not.
  • NOT operator – show you how to negate a Boolean expression by using the NOT operator.

Section 5: Conditional Expressions

  • CASE Expression – add if-then-else logic to the SQL statements.

Section 6: Joining Multiple Tables

  • SQL Aliases – make your query shorter and more understandable.
  • INNER JOIN – introduce you to the join concept and show you how to use the INNER JOIN clause to combine data from multiple tables.
  • LEFT OUTER JOIN – provide you with another kind of joins that allows you to combine data from multiple tables.
  • FULL OUTER JOIN – join multiple tables by including rows from both tables whether or not the rows have matching rows from another table.
  • CROSS JOIN – produce a Cartesian product of rows of the joined tables using the cross join operation.
  • SELF JOIN – join a table to itself using either the inner join or left join clause.

Section 7: Aggregate Functions

  • Overview of Aggregate functions – introduce you to the most commonly used aggregate functions in SQL including AVG, COUNT, SUM, MAX, and MIN.
  • AVG – calculate the average value of a set.
  • COUNT – return the number of items in a set.
  • SUM – return the sum all or distinct items of a set.
  • MAX – find the maximum value in a set.
  • MIN – find the minimum value in a set.

Section 8: Grouping Data

Sqlplus Cheat Sheet 2019

  • GROUP BY– combine rows into groups and apply an aggregate function to each group.
  • HAVING – specify a condition for filtering groups summarized by the GROUP BY clause.
  • GROUPING SETS – generate multiple grouping sets.
  • ROLLUP – generate multiple grouping sets considering the hierarchy of the input columns.
  • CUBE – generate multiple grouping sets for all possible combination of the input columns.

Section 9: Using SET Operators

  • UNION and UNION ALL – combine result set of two or more queries into a single result set using the UNION and UNION ALL operators.
  • INTERSECT – return the intersection of two or more queries using the INTERSECT operator.
  • MINUS – subtract a result set from another result set using the MINUS operator.

Section 10. Subquery

  • Subquery – show you how to nest a query inside another query to form a more flexible query for querying data.
  • Correlated Subquery – introduce you to the correlated subquery which is a subquery that uses values from the outer query.
  • EXISTS – show you how to check for the existence of the row returned from a subquery.
  • ALL – illustrate how to query data by comparing values in a column of the table with a set of columns.
  • ANY – query data if a value in a column of a table matches one of value in a set.

Section 11: Data Manipulation Language (DML) Statements

  • INSERT – insert one or more rows into a table.
  • UPDATE – update existing data in a table.
  • DELETE – delete data from a table permanently.

Section 12: Data Definition Language (DDL) Statements

  • CREATE TABLE – create a new table in the database.
  • ALTER TABLE – modify the structure of an existing table.
  • DROP TABLE – remove the tables permanently.
  • TRUNCATE TABLE – delete all data in a big table fast and efficiently.

Section 13: Constraints

  • PRIMARY KEY – show you how to define a primary key for a table.
  • FOREIGN KEY – walk you through the steps of enforcing the relationship between data in two tables using the foreign key constraint.
  • UNIQUE – ensure the uniqueness of values in a column or a set of columns.
  • NOT NULL – ensure that the values inserted into or updated to a column are not NULL.
  • CHECK – validate data before it is stored in one or more columns based on a Boolean expression.