B/16, First floor, Sant Tukaram Vyapar Sankul

Nigdi, Pune - 411044

8:30 AM - 7:00 PM

Monday to Saturday

SQL Fundamentals

About SQL Fundamentals

The correct pronunciation of SQL is a contentious issue within the database community. In its SQL standard, the American National Standards Institute declared that the official pronunciation is “es queue el.” However, many database professionals have taken to the slang pronunciation “sequel.” The choice is yours.

SQL comes in many flavors. Oracle databases use its proprietary PL/SQL. Microsoft SQL Server makes use of Transact-SQL. All of the variations are based upon the industry standard ANSI SQL. This introduction uses ANSI-compliant SQL commands that work on any modern relational database system.

Course Objective

  • Display data from multiple tables using the ANSI SQL 99 JOIN syntax.
  • Identify the major structural components of the Oracle Database 11g.
  • Create reports of aggregated data.
  • Write SELECT statements that include queries.
  • Retrieve row and column data from tables
  • Run data manipulation statements (DML) in Oracle Database 11g.
  • Create tables to store data.
  • Utilize views to display data.
  • Control database access to specific objects.
  • Write multiple-column sub-queries.
  • Employ SQL functions to retrieve customized data.

• Identify the connection between an ERD and a Relational Database
• Explain the relationship between a database and SQL
• Describe the purpose of DDL
• Describe the purpose of DML
• Build a SELECT statement to retrieve data from an Oracle Database table

• Use the ORDER BY clause to sort SQL query results
• Limit the rows that are retrieved by a query
• Use ampersand substitution to restrict and sort output at runtime
• Use SQL row limiting clause

• Use various types of functions available in SQL
• Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements

• Describe various types of conversion functions that are available in SQL
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Apply general functions and conditional expressions in a SELECT statement

• Describe the use of group functions
• Group data by using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause

• Describe the different types of joins and their features
• Use SELECT statements to access data from more than one table using equijoins and
nonequijoins
• Join a table to itself by using a self-join
• View data that generally does not meet a join condition by using outer joins

• Define subqueries
• Describe the types of problems subqueries can solve
• Describe the types of subqueries
• Query data using correlated subqueries
• Update and delete rows using correlated subqueries
• Use the EXISTS and NOT EXISTS operators
• Use the WITH clause
• Use single-row and multiple-row subqueries

• Describe set operators
• Use a set operator to combine multiple queries into a single query
• Control the order of rows returned

• Truncate data
• Insert rows into a table
• Update rows in a table
• Delete rows from a table
• Control transactions

• Describe data types that are available for columns
• Create a simple table
• Create constraints for tables
• Drop columns and set column UNUSED
• Create and use external tables

• Create a simple and complex view
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

• Differentiate system privileges from object privileges
• Create Users
• Grant System Privileges
• Create and Grant Privileges to a Role
• Change Your Password
• Grant Object Privileges
• How to pass on privileges?
• Revoke Object Privileges

• Add, Modify, and Drop a Column
• Add, Drop, and Defer a Constraint
• How to enable and Disable a Constraint?
• Create and Remove Indexes
• Create a Function-Based Index
• Perform Flashback Operations
• Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
• Query External Tables

• Explain the data dictionary
• Use the Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• Table and Column Information
• Query the dictionary views for constraint information
• Query the dictionary views for view, sequence, index and synonym information
• Add a comment to a table
• Query the dictionary views for comment information

• Use Subqueries to Manipulate Data
• Retrieve Data Using a Subquery as Source
• Insert Using a Subquery as a Target
• Usage of the WITH CHECK OPTION Keyword on DML Statements
• List the types of Multitable INSERT Statements
• Use Multitable INSERT Statements
• Merge rows in a table
• Track Changes in Data over a period of time

• Time Zones
• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• Compare Date and Time in a Session’s Time Zone
• DBTIMEZONE and SESSIONTIMEZONE
• Difference between DATE and TIMESTAMP
• INTERVAL Data Types
• Use EXTRACT, TZ_OFFSET and FROM_TZ
• Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

• Use the Regular Expressions Functions and Conditions in SQL
• Use Meta Characters with Regular Expressions
• Perform a Basic Search using the REGEXP_LIKE function
• Find patterns using the REGEXP_INSTR function
• Extract Substrings using the REGEXP_SUBSTR function
• Replace Patterns Using the REGEXP_REPLACE function
• Usage of Sub-Expressions with Regular Expression Support
• Implement the REGEXP_COUNT function

• Multiple-Column Subqueries
• Pairwise and Nonpairwise Comparison
• Scalar Subquery Expressions
• Solve problems with Correlated Subqueries
• Update and Delete Rows Using Correlated Subqueries
• The EXISTS and NOT EXISTS operators
• Invoke the WITH clause
• The Recursive WITH clause

Course Enquiry Form





In Which Course you are interested :

Your Name

Your Email

Your Contact No

Tell us more