Oracle SQL
( OCP / OVP Paper:1)

Sinet has well experienced academic team to deliver datbase administration using SQL Server..


COURSE DETAILS
BROCHURE

COURSE DETAILS

Duration: 40 hours *

Class: Regular / Week ends / Evening / Sundays

Daily Time: 1.5 hours / 3 hours / half day / Full day

Certification: Sinet Merit Certification

JOIN NOW
* Sinet course can be customized as per student requirement.

JOB PROFILE

Database Administrator

Data Server Manager

Oracle DBA Executive

Oracle DB Support




ORACLE SQL
COURSE CONTENTS

( Can be customized )



1. Introduction in ORACLE SQL

What is DBMS (Database Management System).What is RDBMS (Relational Database Management System).What is oracle. Client / Server Architecture. Login Authentication. Handling data in a data base. What is query?Select query:retrieve all records from a table, retrieve specific columns from a table. Operator Precedence:Multiplication take priority over addition and subtraction. Null values in arithmetic expression, Defining column alias, Concatenation operator, Literal character string, Eliminating duplicate rows, Eliminating duplicate rows.

2. Restricting and sorting data in ORACLE SQL

Limiting rows using a selection, Restrict the rows returned by Using where clause, Comparison conditions- =,<,>,<=,>=,<>, Other comparison operators-Between… And(between two values),IN(match any of a list of values), LIKE (match a character pattern), IS NULL (is a null value) Logical conditions-AND(returns true if both conditions are true), OR(returns true if either conditions is true), NOT(returns true if conditions is false)- Not In(match not of a list of values) Sorting-sort in ascending order and descending order, Sort by column alias, sorting by multiple columns

3. Single row functions in ORACLE SQL

Describe the various type of functions available.Character functions: Case Manipulation functions-LOWER(),UPPER(),INITCAP(), Character Manipulation functions:CONCAT(),SUBSTR(),LENGTH(),INSTR(),LPAD(),RPAD(),TRIM(),REPLACE() Number/Mathematical functions-ROUND(),TRUNC(),MOD()Date functions:SYSDATE,MONTHS_BETWEEN(),ADD_MONTHS(),NEXT_DAY(),LAST_DAY(),ROUND(),TRUNC(),Arithematic with date Conversion Functions:TO_CHAR() with dates, elements of date format, TO_CHAR function with numbers, TO_NUMBER(),TO_DATE() Nesting functions-single row functions can be nested to any level. General functions-work with any data type, NVL(), NVL2(), NULLIF(), COALESCE()

4. Conditional Expressions in ORACLE SQL

Conditional expressions use of if then else login within a sql statements. CASE expression: facilitates conditional inquires by doing the work of an if…then…else statement. DECODE function: facilitates conditional inquires by doing the work of a CASE or if…then…else statement.

5. Displaying data from multiple tables in ORACLE SQL

Cartesian product/ Cross join: joins one or more table without specify criteria. Equijoins/inner join: joins one or more tables specify criteria. Using table aliases: simplify queries by using table aliases. Retrieving record with nonequijoins.Outer join: join to also see rows that do not meet join condition. Self join: joining a table to itself. Natural Join: join two table that have equal values in all matched columns. Creating Joins with the USING clause. Creating Joins with the ON clause. Left outer join: joins two tables, includes all rows from the first table and matching. Right outer join: joins tables, includes all rows from the second table and matching. Full outer join: joins tables, includes all rows from first and second table corresponding matching rows.

6. Aggregate data using group functions in ORACLE SQL

The Group by clause is returns an aggregate value for the groups defined by the select statement. Type of Group Functions: AVG(),COUNT(),MAX(),MIN(),SUM() Using Distinct Keyword. Grouping by More than one column. The Having Clause is used to select particular rows from the result set obtained using the group by clause. Nesting Group function.

7. subqueries in ORACLE SQL

Define Sub query, sub query syntax, type of sub query. Single Row subqueries: returns only one row. Using comparison operators, group function in a subquery, Having clause with subquery. Multiple row subqueries: return more than one row. Using ANY operator Using All operator

8. Substitution variables in ORACLE SQL

Using the & substitution variable, Character and date values with substitution variables. Specifying column names, expressions and text. Using the && substitution variable: reuse the variable value without prompting.

9. Manipulating data in ORACLE SQL

Data Manipulation Language(DML) Adding a new row to a table using Insert statement, inserting rows with null values, insert specified values to table, copying rows from another table. Edit data in a table using update statement, update two columns with subquery, updating rows based on another table. Remove a row from table using Delete Statement, deleting rows based on another table. Using a subquery in an insert statement. With check option keyword. Merge Statement.

10. Configuring and managing in ORACLE SQL

Naming rules Create table using CREATE TABLE statement. Querying in data dictionary : user_tables,user_objects, user_catalog. Datatypes, datatime data types. Add a new column using ALTER command, modify an existing table using ALTER command, Drop a column using ALTER. Rename table using RENAME command. Delete table using TRUNCATE. Adding comments to a table.

11. Constraints (1) in ORACLE SQL

What are constraints? Defining constraints. Not null constraint: is defined at the column level Primary key: requires the values in the column, to which it is attached to be unique and contain values other than NULL. Set primary key on table creation. Remove primary key from table. Set primary key to existing table Unique key-- requires the values in the column, to which it is attached to be unique. Set unique key on table creation. Remove unique key from table. Set unique key to existing table

12. Constraints (2) in ORACLE SQL

Foreign key refers to a column of another table or another column of the same table. Set foreign key on table creation,. Remove foreign key from table. Set foreign key to existing table Check constraint: defines a condition that each row must satisfy.

13. View in ORACLE SQL

View: Views are logical representation of data contained in a table. Why use Views. Create View, edit view using CREATE OR REPLACE VIEW, Creating complex view. Using with CHECK option. Denying DML operations .Remove view using DROP VIEW. Inline Views. Top-n analysis: the n largest or smallest values of a column.

14. Other database objects in ORACLE SQL

Define a sequence to generate sequential numbers automatically. Create sequence using SEQUENCE statement. Confirming sequences, NEXTVAL: return next available sequence value. CURRVAL: obtains the current sequence value Modifying sequence Remove Sequence

15 Index in ORACLE SQL

Index: The process of retrieving data, index also quickens the various select, updates and deletes issued on a table. Create index using CREATE INDEX statement. When to create an index. Confirming indexes. Function based index: based on an expressions. Remove index using DROP. Synonyms: Simply access to objects .Create synonym using CREATE SYNONYM. Remove synonym using DROP.

16. Controlling user access in ORACLE SQL

Creating users-DBA creates users by the CREATE USER statement. Grant privilege to users: grant specific system privileges to a user. Role: named group of related privileges. Creating and granting privileges to a role using CREATE ROLE and GRANT. Change user password using ALTER statement. Object privilege: vary from object to object. Grant object privilege using GRANT. Using WITH GRANT OPTION and PUBLIC Keyword. Confirming Privileges granted.

17. SET Operators in ORACLE SQL

SET operator to combine multiple queries into a single query. UNION: returns results from both queries after eliminating duplications. UNION ALL: returns results from both queries including all duplications. INTERSECT: returns results that are common to both queries. MINUS: returns results rows from the first query that are not present in the second query.

18. Enhancements to the group by clause in ORACLE SQL

Review of the GROUP By clause, Review of HAVING clause. ROLLUP operator: an extension to the group by clause. CUBE operator: extension to the group by clause. Grouping function can be used with either the CUBE or ROLLUP operator. Grouping set are a further extension of the group by clause. Composite columns: collection of columns that are treated as a unit. Concatenated groupings.

19. Advanced Subquerie in ORACLE SQL

Subquery is a select statement embedded in a clause of another SQL statement. Multiple column subquery, pairwise comparison subquery, pairwise comparison subquery, usinf subquery in the FROM clause , Scalar subquery expressions,Correlated subquery Exists operator, Not exists operator, Correlated update, correlated delete, With clause.

20. Hierarchical retrieval in ORACLE SQL

From the Bottom Up,From the top Down, Formatting hierarchical reports using LEVEL and LPAD. DML and DDl statements Multitable conditional insert all Conditional first Insert statement Pivoting insert create index with table statement

21. PRACTICE (1) in ORACLE SQL

PRACTICE QUESTIONS

22. PRACTICE (2) in ORACLE SQL

PRACTICE QUESTIONS

23. PRACTICE (3) in ORACLE SQL

PRACTICE QUESTIONS

24. PRACTICE (4) in ORACLE SQL

PRACTICE QUESTIONS

25. PRACTICE (5) in ORACLE SQL

PRACTICE QUESTIONS



SINET F A Q

Frequently asked question resources







What is Microsoft ASP.Net ?

ASP.NET is an open-source server-side web-application framework designed for web development to produce dynamic web pages developed by Microsoft to allow programmers to build dynamic web sites, applications and services.

Is Sinet provides Microsoft ASP.Net and MVC education with training ?

Sinet is the leader of web technologies and framework education and training including Microsoft development technologies in Kerala India. Hundreds of satisfied programmers are working across India, US, Europe and in middle east.

What is C Programming language ?

C is a general-purpose, procedural computer programming language supporting structured programming, lexical variable scope, and recursion, while a static type system prevents unintended operations, designed by Dennis Ritchie.

What is CPP (C++) Programming language ? Is Sinet provides C and C++ programming courses ?

C++ is a high-level, general-purpose programming language created by Bjarne Stroustrup as an extension of the C programming language, or "C with Classes".
Yes. Sinet provides C and Cpp programming courses professionally. Sinet is among the forefront of leading system programming educators in Kerala India.

What is Microsoft C#.Net ?

C# is a general-purpose, multi-paradigm programming language encompassing strong typing, lexically scoped, imperative, declarative, functional, generic, object-oriented, and component-oriented programming disciplines.

Is Sinet provides Microsoft C#.Net and SQLServer education with training ?

Sinet is the training leader of Microsoft dot net Programming technologies and database administration. We provides live and academic project training to students in any category using Microsoft development technologies in Kerala India.

What is Java Programming Language ?

Java is a general-purpose programming language that is class-based, object-oriented, and designed to have as few implementation dependencies as possible.Designed by: James Gosling

Is Sinet provides Oracle Java and MySQL education with training ?

Sinet is the training leader of Oracle Java technologies and database administration.

What is meant by Microsoft Office?

Microsoft Office is a suite of desktop productivity applications that is designed specifically to be used for office or business use. It is a proprietary product of Microsoft Corporation and was first released in 1990. It mainly consists of Word, Excel, PowerPoint, Access, OneNote, Outlook and Publisher applications.

Is Sinet provides course in Full stack Microsoft office with Job training ?

Yes. Sinet is the leading provider of full-stack MSO and office automation with placement assistance.

What is microsoft office 365 ?

Office 365 is an integrated experience of apps and services, designed to help you pursue your passion and grow your business. Get apps like Word, Excel, PowerPoint, and more, updated monthly with the latest features and security updates. Use cloud-powered email to reach customers and coworkers wherever work takes you.

What is Microsoft Excel and what is it used for?

Microsoft Excel is a spreadsheet program. That means it's used to create grids of text, numbers and formulas specifying calculations. That's extremely valuable for many businesses, which use it to record expenditures and income, plan budgets, chart data and succinctly present fiscal results.

What is the main function of Microsoft Excel?

Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value, and minimum value for a range of cells. In order to use functions correctly, you'll need to understand the different parts of a function and how to create arguments to calculate values and cell references.

What is Oracle best known for?

The company is best known for its Oracle database software, a relational database management system, and for computer systems and software, such as Solaris and Java, acquired in its purchase of Sun Microsystems in 2010. Oracle is based in Redwood Shores, California.

Is Sinet provides course in Oracle DBA education with training ?

Sinet is the training provider of Oracle technologies and database administration.

What is PHP ?

PHP is a popular general-purpose scripting language that is especially suited to web development. It was originally created by Rasmus Lerdorf in 1994; the PHP reference implementation is now produced by The PHP Group.

Is Sinet provides PHP, Laravel and Codeigniter MVC education, training and Job-Placements ?

Yes. Sinet is the leader in web technologies, framework education, training and placements. We provide PHP core and MVC development technologies across Kerala India. Hundreds of satisfied programmers are working in India, US, Europe and in the middle east.

What is Laravel and why it is used?

Laravel is an open-source PHP framework designed to make developing web apps easier and faster through built-in features. These features are part of what makes Laravel so widely used by web developers: A modular packaging system with dependency management. This means you can easily add functionalities to your Laravel app without writing them from scratch. You can either create your own packages for code you routinely use or install ready-to-use packages through Composer. A complete authentication system Object-relational mapping. Eloquent ORM included with Laravel presents database tables as classes for easier data access and manipulation. A command-line interface (CLI) that comes with dozens of pre-built commands (Artisan). Automatic testing. Automated tests are provided as an integral part of Laravel. A portable, virtual development environment. Homestead provides developers with all the tools necessary to develop Laravel straight out of the box.

Is Laravel a CMS?

Unlike CMS's like Drupal or Wordpress, Laravel gives you full control over your application. In Laravel everything is done in code, unlike Drupal or Joomla, for example, where you can create functional websites without writing a single line of code or without even knowing what PHP is. Simply put, a CMS is an app that comes with basic functionalities and is built on top of a framework. Laravel is a framework and it’s used to build apps, including CMS platforms.

What is CodeIgniter used for?

CodeIgniter is a PHP MVC framework used for developing web applications rapidly. CodeIgniter provides out of the box libraries for connecting to the database and performing various operations like sending emails, uploading files, managing sessions, etc

Is CodeIgniter a CMS?

It is not a CMS, but a starting point for new projects build with CodeIgniter that require ready-made tools like: User Management with Role-Based Access Control. Fully Modular codebase built around HMVC. Powerful, parent/child capable theme engine.

What is PHP and why it is used?

PHP is a server side scripting language. that is used to develop Static websites or Dynamic websites or Web applications. PHP stands for Hypertext Pre-processor, that earlier stood for Personal Home Pages. PHP scripts can only be interpreted on a server that has PHP installed.

What is Python Language ?

Python is an interpreted, high-level, general-purpose programming language. Created by Guido van Rossum and first released in 1991, Python's design philosophy emphasizes code readability with its notable use of significant whitespace.

Is Sinet provides course in Python and Django with training ?

Sinet is the training provider of full stack Python and Django technologies with MYSQL .

What is Python language basics?

Python is an object-oriented programming language created by Guido Rossum in 1989. It is ideally designed for rapid prototyping of complex applications. ... Python programming is widely used in Artificial Intelligence, Natural Language Generation, Neural Networks and other advanced fields of Computer Science.

What is the difference between Python and Django?

Django is a web application framework that uses Python. Python is a programming language. Django is a web framework which is coded in Python. So, the difference is just like the difference between a given programming language and the software that is built using it.

Is Python used for games?

Python is also good for building tools for game designers which simplify tasks like level design or dialog tree creation and having those tools export that work into a format the main game engine can use. Some game engines might use Python as a scripting language.

what is python django ?

Django is a free and open source web application framework written in Python. A framework is nothing more than a collection of modules that make development easier. ... The official project site describes Django as "a high-level Python Web framework that encourages rapid development and clean, pragmatic design.

How long does it take to learn Python?

Basic Python is where you get to learn syntax, keywords, if-else, loops, data types, functions, classes and exception handling, etc. An average programmer may take around 2–4 weeks to get acquainted with these basics.

What is sql server? Is Sinet provides course in Microsoft SQL Server with training ?

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Sinet is the training provider of SQL Server technologies and database administration.

What is difference between SQL and SQL Server?

The main difference between SQL and MS SQL is that SQL is a query language that is used in relation databases whereas MS SQL Server is itself a relational database management system (RDBMS) developed by Microsoft. A DBMS is a software that is used to manage the database.

What is responsive web design ?

Responsive web design is an approach to web design that makes web pages render well on a variety of devices and window or screen sizes. Recent work also considers the viewer proximity as part of the viewing context as an extension for RWD.

Is Sinet provides course in Full stack Web Designing with Job training ?

Yes. Sinet is the leading provider of full-stack responsive web designing and digital marketing technologies with placement assistance.

What is ASP NET MVC used for?

ASP.NET MVC is an open source web development framework from Microsoft that provides a Model View Controller architecture. ASP.net MVC offers an alternative to ASP.net web forms for building web applications. It is a part of the . Net platform for building, deploying and running web apps.

Is ASP NET MVC cross platform?

When it comes to web application development, there are multiple technologies available to choose from. There are open-source technologies like Java & PHP, and then, there is closed-source technology ASP.NET MVC. ... ASP.NET Core is an open-source, cross-platform framework developed by both the Microsoft and its community.

Should I learn .NET or .NET core?

ASP.NET Core is the next version of ASP.NET MVC. You should learn ASP.Net Core because ASP.NET Core is an open-source, cross-platform framework for building modern, cloud-based web apps on Windows, macOS, or Linux.

Is Job Placement support available in Sinet Software Institute?

Yes. Sinet has effective job placement system for students. Its starts from education, Job training, recognition merit certifications, experience certificates after completion of training programs and finally placement activities. Hundreds of alumni students working here and abroad are our pride.

Can Sinet provide multinational company certifications like Microsoft, Oracle, SAP while learning at Sinet Institute?

Yes. Sinet has been providing MNC certifications by collaboration with various testing centres as educational partners.

Is Sinet institute got any ISO standard of recognition?

Yes. Sinet has been maintaining its quality and as a result of this effort, Sinet is ISO 9001: 2015 certified educational organization.

Is free wi-fi available in Sinet Software Institute?

Yes. Sinet provides free wi-fi and high speed internet to students inside Sinet Software Institute campus

Free warranty against technology obsolescence is available at Sinet Software Institute?

Yes. Sinet provides warranty against technology obsolescence and students will get the added advantage of latest technology even they admitted for old versions.

Separate boys and girls Hostel facility is available at Sinet Software Institute?

Yes. Sinet provides hostel facility to our students by affiliation with privately managed hostels.

Is parking facility available in Sinet Perinthalmanna?

Yes. Sinet provides sperate parking for cars and bikes with the supervision of skilled security personal





SINET IS AN ISO 9001 : 2015 CERTIFIED SOFTWARE TRAINING INSTITUTE

(Division of Sinet Education)

www.sinetedu.com