Sqlplus Cheat Sheet

Posted : admin On 1/2/2022

The regular penetration testing could significantly improve the company's security. The auditor shall obtain all necessary rights and permissions to conduct penetration tests from the owner of the target network or from the owner of target system before conducting any audit. Oracle Cheat Sqlplus Commands d2nvmy6529nk. Oracle Cheat Sqlplus Commands d2nvmy6529nk. Home (current) Explore Explore All. Bloomberg Commands Cheat Sheet November 2019 112. More Documents from 'Dong Song' Oracle Cheat Sqlplus Commands December 2019 35. Mary Balogh Intre Decenta Si Dorinta.

SQL How To ...

These notes are intended to provide a simplified crib sheet (or reminder) on SQL. It is not a tutorial. A number of examples for common types of tasks are provided - but little or no explanation.

SQL - Structured Query Language - is a language understood by most database systems. Except where noted it is believed these SQL statements will work with Microsoft SQL Server, Oracle and MySQL.

Index

Select

Return all records all columns in a table:

Return all records but only field1 and field2 in a table:

Return field1 for all records in a table with a specific value for field2:

Return all records in a table where field1 is one of three possible values:

Return the number of records in a table:

Return the number of records in a table with a specific value for field2:

Simple join:

or

select table1.field1, table2.fieldA from table1, table2
where table1.field2=table2.fieldB

or

select table1.field1, table2.fieldA
from table1 inner join table2 on table1.field2 = table2.fieldB

Select all unique values in field1 from a table (not supported in MSAccess):

or (works in MSAccess):

To get a count of the number of unique values in a field (not supported in MSAccess):

selet count(distinct field1) from TableName

For MSAccess use:

Sql syntax cheat sheet

Select all unique values for field1 from a table together with the number of records with that unique value:

Select all unique values for combinations of field1 and field2 from a table together with the number of records with that combination:

Select the number of unique values:

Select all duplicate records in a table, where two (or more) records are considered duplicates if they share a common value for a single field:

Oracle Sql Cheat Sheet Pdf

Select all duplicate records in a table, where two (or more) records are considered duplicates if they share common values for a pair of fields:

Select similar records, i.e. all records which have duplicate field1 and field2 in a table but with different field3 (i.e. specifying which fields must be the same and which different):

Note:

Sqlplus Cheat Sheets

  • It is important to specify at least one field which is different between the two records otherwise this query will list a record as being the same as itself.
  • This query will not find duplicate records, i.e. records with every field the same.

Select all records from a table which do not share a common ID with records from a second table:

Note:

  • Sub-queries are quite slow.
  • Sub-queries are not supported in versions of MySQL prior to MySQL 5, so the above will not work on older versions of MySQL. My thanks to Kevin Bowman for pointing out that MySQL 5 supports sub-queries.

An alternative using a join (which can be much faster):

select table1.* from table1
left join table2 on table1.field1 = table2.field2
where table2.field2 is null;

The following method (which has been suggested by Michael Miller) is to use EXISTS. It is much faster on SQL Server than the above (but Michael says it is comparable with the left join technique on Oracle):

select * from table1
where not exists (select field2 from table2 where table2.field2 = table1.field1)

To perform a two way join:

select * from
table1 left join table2 on table1.field1 = table2.field1,
table1 left join table3 on table1.field2 = table3.field3

this has been tested on SQL Server, but not on Oracle or MySql. It does not work with MS-Access.

To combine the results of two queries (be aware that the number and types of fields in both queries must agree):

To return a value based on the contents of a field. This can be done using either Iif, Decode or Case, depending on the database.

The following works with MSAccess:

This is equivalent to the following on SqlServer:

For Oracle use the DECODE function.

To create a new table to hold the results of the select query:

Be aware that this will fail if table2 exists, and that the new table will be created without any indexes. This isn't supported on MySQL.

Insert

Insert new record into a table:

Insert new record into a table explicitly naming fields:

insert into TableName (field1,field2,field3) values (1,2,3)

Insert new record into a table using values from another table:

MySQL (but not Oracle or SQL Server) allow a single insert statement to insert multiple rows rather than once at a time:

insert into TableName (field1,field2,field3)
values (1,2,3),(4,5,6)

Update

Update all records in a table:

Update specific records in a table:

To update more than one field at a time:

Update a field in a table using a value from another table where both records are referenced by a common key - warning, different databases support different syntax!

This works in MS-Access and MySQL (5) but not in SQL Server:

update TableOne
inner join TableTwo on TableOne.commonID = TableTwo.commonID
set TableOne.field1 = TableTwo.fieldX

or

This works in MS-Access and MySQL but not in SQL Server:

or

This works in SQL Server (my thanks to John Lee for this), but not in MS-Access or MySQL:

Note:

  • MS-Access gives the error 'Operation must use an updateable query' if you attempt to use any of the above with a view/query rather than a table. The work around is to copy the data from the query into a temporary table and use the temporary table instead.

Delete

Delete all records in a table (dangerous):

Delete specific records in a table:

Delete records from one table which do not have a matching field in another table:

Keys

Be aware that there are often subtle syntax variations between different database systems. Also other key properties (for example 'clustered') will vary between database systems. Therefore please treat this part of the SQL crib sheet as a guide only.

Create a primary key on a table:

Alter Table TheTable Add Primary Key (field1, field2)

To add an index on a field:

To remove a primary key:

About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.

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.

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

Sql Plus Cheat Sheet

  • 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

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

Section 11: Data Manipulation Language (DML) Statements

Sqlplus Cheat Sheet Pdf

  • 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

Sqlplus cheat sheets
  • 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.