Udemy Oracle SQL
-
What is Data?
= Data means the information that we store somewhere to use later.
EG - If you type something in a notebook, it is data, or if you store something in a text file, or into an Excel spreadsheet, or into some other places like databases, they are all called
data. Whatever information you store somewhere is calledData.
For example, you are using your phones and using lots of applications. All your actions produce some data in some databases.
-
What is Database? = A database is basically a place where we store an organized collection of data. This means it is an electronic system that allows us to store the data, then easily access it, manipulate it, or retrieve it efficiently.
-
The database mainly consists of TABLEs. And these tables consist of COLUMNs and ROWs.
-
In real usage, a database consists of storage, memory, processor, and software. So with this software, we can easily store and organize our data into that storage devices. They might be different in different databases, but you can think of them as the hard drives on your computer. Most basically, a database is a software that you can install into your computer or some other place that organizes your data systematically, then you can easily query and manipulate that data efficiently, no matter how big it is!
-
What is DBMS? = Database management systems are the collections of programs that enabled users to access the database, manipulate the data and retrieve the data to the users.
-
DBMS translates SQL queries sent by the user or by other software programs.
-
DBMS manages access to the data by users.
-
DBMS can stop functioning if it starts using too much memory.
-
DBMS manages transactions. DBMS is responsible for
-
Communicating with the operating system.
-
Translating SQL queries sent by the user.
-
Transaction management.
-
User access
-
Organization of the data in the database.
There are a few types of database management systems, We'll discuss major 4 types of them:-
-
Hierarchical DBMS: - This type of DBMS uses the parent-child relationship for storing the data. It stores the data like a tree with nodes and branches. The registry usage of Windows XP is a good example of this DBMS type, but this type of DBMS is so rare nowadays, so no need to go further for this.
-
Network DBMS:- RDM Server is an example of this type. This type uses many-to-many relations, but this type generally results in complex database structures. So it's not a frequently used database management systems model.
-
Relational Database Management Systems:- This type is the most used database management system nowadays. The Oracle database, MySQL, MsSQL, and many others use this type of database management system. Unlike the Network DBMS, Relational DBMS does not support many-to-many relationships. Relational DBMS has some entities basically mean tables and we establish some relations between these entities. This type of DBMS has predefined types and you can store only these types in it.
-
Object Relational Database Management Systems:- This type supports storing new data types because you can have your own objects and each object is a type in it. So you can have your own type in it. The objects here have attributes, which means the data and the methods that define what to do with this data. If you are familiar with an object-oriented language, you will understand that better. This is also a useful database management system and maybe the most known example of that type is PostgreSQL.
These are the 4 most known database management systems, but the most used one is the relational database management system, which is also called RDBMS, and Oracle uses RDBMS in their databases.
The RDBMS system manages the relational data. Oracle Database is the most famous relational database system (RDBMS) because it shares the largest part of a market among other relational databases. Some other popular relational databases are MySQL, DB2, SQL Server, PostgreSQL, etc.
-
Oracle database is a relational database management system (RDBMS) from Oracle Corporation.
-
Oracle database is a relational database management system. It is also called OracleDB, or simply Oracle. It is produced and marketed by Oracle Corporation. It was created in 1977 by Lawrence Ellison and other engineers. It is one of the most popular relational database engines in the IT market for storing, organizing, and retrieving data.
-
Oracle database was the first DB designed for enterprise grid computing and data warehousing. Enterprise grid computing provides the most flexible and cost-effective way to manage information and applications. It uses SQL queries as a language for interacting with the database.
Oracle database is compatible with a wide range of platforms such as Windows, UNIX, Linux, and macOS. It supports several operating systems like IBM AIX, HP-UX, Linux, Microsoft Windows Server, Solaris, SunOS, macOS, etc. In the late 1990s, Oracle began supporting open platforms like GNU/Linux.
The following is a list of Oracle database editions in order of priority:
Enterprise Edition: It is the most robust and secure edition. It offers all features, including superior performance and security.
Standard Edition: It provides the base functionality for users that do not require Enterprise Edition's robust package.
Express Edition (XE): It is the lightweight, free, and limited Windows, and Linux edition. Oracle Lite: It is designed for mobile devices.
Personal Edition: It's comparable to the Enterprise Edition but without the Oracle Real Application Clusters feature.
-
Oracle Corporation is the largest software company to develop and markets computer software applications for business. The company is best known for its Oracle database products and, more recently, cloud products and services. Its relational database was the first to support SQL, which has since become the industry standard.
-
Oracle database is one of the most trusted and widely used relational database engines. The biggest rival of the Oracle database is Microsoft's SQL Server.
Oracle Corporation was founded by Lawrence Ellison (Larry Ellison), Bob Miner, Ed Oates, and Bruce Scott in August 1977. They have a lot of experience in building database programs for several companies and build their first project (a special database program) for the CIA (Central Intelligence Agency). Oracle was named after "Project Oracle," a project for one of their clients named Central Intelligence Agency, and the company that created Oracle was called Systems Development Labs (SDL). Systems Development Labs was renamed Relational Software Inc. (RSI) in 1978 to expand its market for the new database. They again changed the name of the company from RSI to Oracle Systems Corporation in 1982.
The first commercially available RDBMS named Oracle V2 (Version 2) was built using PDP-11 assembler language (SQL-based RDBMS). Although they already developed a commercial RDBMS in 1977, it wasn't available for purchase until 1979, when Oracle version 2 was released.
In 1983, Oracle database portable version named "Oracle version 3" was released. This version was written in the C programming language. It was the first relational database that can run in mainframes, minicomputers, PCs, or any hardware with a C compiler. It also supports SQL queries and transaction execution.
The other subsequent versions are:
In 1984, Oracle 4 was released that supports Transactions [Commit/Rollback], export/import utilities, and the report writer.
In 1985, Oracle 5 was released, which provides support for Client-Server Architecture. This new feature has the capability to connect the client's software to a database server through a network.
In 1989, Oracle 6 added support for PL/SQL language. It also comes with new features such as OLTP high-speed systems, hot backup capability, and row-level locking.
In 1992, Oracle 7 was released. This version comes to the market as a result of four years of hard work and two years of customer testing. It added some exciting features and capabilities in the area of security, administration, development, and performance.
In 1997, Oracle 8 was released. This version comes with the support of ORDBMS which was designed to work with Oracle's network computer (NC). It also added support for Java, HTML, and OLTP.
In 1998, Oracle 8i was released. Here 'I' stands for Internet. It was the first database version that added support for Web technologies such as Java and HTTP.
In 2001, Oracle 9i was released with 400 new features such as XML, RAC (Real Application Clusters), etc. These features reduce database size and provide high availability & enhanced performance.
In 2003, Oracle 10g was released with grid computing technology means grid. It was the first version that supports 64-bit LINUX OS.
In 2006, Oracle 11g was released. This version comes with new features such as Oracle Database Replay, Transaction Management using Log Miner, Virtual Column Partitioning, Case sensitive passwords, Online Patching, Parallel Backups on the same file using RMAN, and many others.
In July 2014, Oracle 12C was released with Cloud support.
In Feb 2018, Oracle 18 C was released. This version was the world's first autonomous database.
- It is one of the oldest database management companies in the world. It has always focused on enterprise needs and kept up with the newest technological developments. As a result, its products are constantly updated with new features. For example, the most recent Oracle database, 19C, is also available on Oracle Cloud. Oracle allows users to select from various database editions to meet their specific demands with a cost-effective solution.
Oracle database manages data with the help of an open, complete, and integrated approach. The following are features that complete the demand for powerful database management:
Availability: It is never offline or out of service which means supported 24*7 availability of the database. It provides high availability of databases because of the Oracle Data Guard functionality. This functionality allows using of the secondary database as a copy of the primary database during any failure. As a result, all normal processes such as backups and partial failures do not interrupt the database from being used.
Security: Oracle has a mechanism for controlling and accessing the database to prevent unauthorized access. It provides high security because of the Oracle Advanced Security features. It offers two solutions to protect databases that are TDE (Transparent Data Encryption) and Data Redaction. TDE supports data encryption both at the source and after export. Redaction is performed at the application level. Oracle has some other security features like Oracle Database Vault that regulates user privileges and Oracle Label Security.
Scalability: It provides features like RAC (Real Application Cluster) and Portability, which makes an Oracle database scalable based on usage. In a clustered environment, it includes capabilities such as rolling instance migrations, performing upgrades, maintaining application continuity, quality of service management, etc.
Performance: Oracle provides performance optimization tools such as Oracle Advanced Compression, Oracle Database In-Memory, Oracle Real Application Testing, and Oracle Times Ten Application-Tier Database Cache. Their main objective is to improve system performance to the highest possible level.
Analytics: Oracle has the following solutions in the field of analytics:
OLAP (Oracle Analytic Processing): It is an implementation of Oracle for doing complicated analytical calculations on business data.
Oracle Advanced Analytics: It is a technical combination of Oracle R Enterprise and Oracle Data Mining that assists customers in determining predictive business models through data and text mining, as well as statistical data computation.
Management: Oracle Multitenant is a database management tool that combines a single container database with many pluggable databases in a consolidated design.
The following are the main advantages of an Oracle database:
Performance: Oracle has procedures and principles that help us to get high levels of database performance. We can increase query execution time and operations with the use of performance optimization techniques in its database. This technique helps to retrieve and alter data faster.
Portability: The Oracle database can be ported on all different platforms than any of its competitors. We can use this database on around 20 networking protocols as well as over 100 hardware platforms. This database makes it simple to write an Oracle application by making changes to the OS and hardware in a secure manner.
Backup and Recovery: It is always better to take a proper backup of your entire Oracle online backup and recovery. The Oracle database makes it easy to accomplish recovery quickly by using the. RMAN (Recovery Manager) functionality. It can recover or restore database files during downtime or outages. It can be used for online backups, archived backups, and continuous archiving. We can also use SQL* PLUS for recovery, which is known as user-managed recovery.
PL/SQL: One of the greatest benefits of using the Oracle database is to support PL/SQL extension for procedural programming.
Multiple Database: Oracle database allows several database instances management on a single server. It provides an instance caging approach for managing CPU allocations on a server hosting database instances. Database resource management and instance caging can work together to manage services across multiple instances.
Flashback Technology: This advantage comes with the recent Oracle version. It allows us to recover those data that are incorrectly deleted or lost by human errors like accidental deletion of valuable data, deleting the wrong data, or dropping the table.
The following are the disadvantages of the Oracle database:
Complexity: Oracle is not recommended to use when the users are not technically savvy and have limited technical skills required to deal with the Oracle Database. It is also not advised to use if the company is looking for a database with limited functionality and easy-to-use.
Cost: The price of Oracle products is very high in comparison to other databases. Therefore users are more likely to choose other less expensive options such as MS SQL Server, MySQL, etc.
Difficult to manage: Oracle databases are often much more complex and difficult in terms of the management of certain activities.
-
Security:- Oracle is a very secure database. It has both its own security things and its design also enables you to create really secure codes.
-
Performance:- Especially, if you work with a large amount of data, you will most probably have performance issues with the other databases. But Oracle handles that with its optimizer, tuning options, and the hardware they offer to us that works perfectly fast with the database. There are a lot of reasons that Oracle is really good for performance.
-
Scalability:- You can have a great amount of data in your tables without any problems. Because, nowadays, we have a huge amount of data, and storing them in one source is sometimes a huge problem for the databases. But Oracle has great options for that. So you can scale your data without any problems.
-
Powerful Coding:- Oracle SQL and PL/SQL have so many features that the other databases are far away from that abilities. With these languages, You can create faster, stronger, and much capable codes.
-
My Oracle Support:- this is a really important thing, especially for the companies. Because, while the data in your database grows with all the complexity of your business, sometimes you may stuck somewhere and cannot solve that problem. At that time, you can easily reach the official Oracle employees and solve your problems together. I know that this is really useful because no one can know the database more than its developers. And you can reach them individually. That is great. Besides, you can get support for each product of Oracle. However, I have to say that you need to pay for this. You don’t have to get that support if you believe that you can solve your problems alone. But there will be some times that you will extremely need that support. That is a great ease.
- Oracle is pretty stable on deadlocks.
- Oracle can run on multiple Operating Systems. Or Oracle supports more than 20 programming languages.
-
You must know how to deal with that database:- Because it enables you to do so many things, you may have some unintentional results if you do something without knowing. Like using a plane instead of a car, you need to know what you are doing. Otherwise, the consequences may not be good. You don’t lose the data of course, because Oracle guarantees you did not lose your data, but you may need more hardware, you may have memory issues, etc. But this is not about the database. This is about what you did since you did something wrong.
-
It is not free:- And sometimes more expensive than many others. But, according to your needs, you can decrease that cost. Actually, decreasing the cost is not your job. Your managers and DBAs do that. But, it is free for personal use. If you want to use that database at home, you don’t pay for it. But if you will use that in your company, I mean for commercial use, you need to pay for this. So, if you want to choose a database, you will need to make your choice against the price and capabilities. But if we think about the capabilities and performance, Oracle is the best for now.
-
According to "www.siftery.com":- Facebook, IBM, LinkedIn, PayPal, JP Morgan, Intel, Amazon, etc are using Oracle Database.
-
Basic units of data storage in an Oracle database. Here, data is stored in rows and columns. You define a table with a table name and a set of columns.
-
Tables are the basic unit of data storage in an Oracle database. They hold all user-accessible data. Each table has columns and rows. In an employees table, for example, there can be columns called last_name and employee_id. Each row in the table would contain a value for the employee name and number.
-
Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record.
Relational Database Management System
- A relational database is a type of database that stores and provides access to data points that are related to one another. Relational databases are based on the relational model, an intuitive, straightforward way of representing data in tables. In a relational database, each row in the table is a record with a unique ID called the key. The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish relationships among data points.
A relational database consists of a collection of Tables. Fields are the column of the relation or tables. Records are each row in relation. Keys are the constraints in a relation.
A relational database example
-
Here’s a simple example of two tables a small business might use to process orders for its products. The first table is a customer info table, so each record includes a customer’s name, address, shipping and billing information, phone number, and other contact information. Each bit of information (each attribute) is in its own column, and the database assigns a unique ID (a key) to each row. In the second table—a customer order table—each record includes the ID of the customer that placed the order, the product ordered, the quantity, the selected size and color, and so on—but not the customer’s name or contact information.
-
These two tables have only one thing in common: the ID column (the key). But because of that common column, the relational database can create a relationship between the two tables. Then, when the company’s order processing application submits an order to the database, the database can go to the customer order table, pull the correct information about the product order, and use the customer ID from that table to look up the customer’s billing and shipping information in the customer info table. The warehouse can then pull the correct product, the customer can receive timely delivery of the order, and the company can get paid.
The simple yet powerful relational model is used by organizations of all types and sizes for a broad variety of information needs. Relational databases are used to track inventories, process e-commerce transactions, manage huge amounts of mission-critical customer information, and much more. A relational database can be considered for any information need in which data points relate to each other and must be managed in a secure, rules-based, consistent way.
Relational databases have been around since the 1970s. Today, the advantages of the relational model continue to make it the most widely accepted model for databases.
-
Accurate:- Since data is stored just once, it eliminates the data deduplication. If not, the same data would be in so many places, and in time, they would be different than the others since you may forget to change them all. So in relational databases, data will be accurate since it is stored in only one place.
-
Flexible:- If you don’t use a relational database design, it would be so hard to get data from different tables. But with relational databases, you can easily do that. Besides, you can easily run complex queries within relational databases.
-
Collaborative:- Multiple users can access the same database. This is true for all systems.
-
Trusted:- Relational database models are being used for so long time. So they are mature and well-understood. So we can trust their reliabilities.
-
Secure:- The data in tables can be limited to allow access by only particular users. So you can easily secure your tables against any change or any users.
Four crucial properties define relational database transactions: atomicity, consistency, isolation, and durability—typically referred to as ACID.
- Atomicity defines all the elements that make up a complete database transaction.
- Consistency defines the rules for maintaining data points in a correct state after a transaction.
- Isolation keeps the effect of a transaction invisible to others until it is committed, to avoid confusion.
- Durability ensures that data changes become permanent once the transaction is committed.
= The smallest unit that can contain a meaningful set of data is called as "entity". So our rows, columns, and even tables are entities. We can think of entities as the objects of databases. But in this lecture, we will basically think of entities as tables. And entities have some attributes. So we can think of the data in the tables as the attributes.
- An entity is a “thing” or “object” in the real world that is distinguishable from all other objects. For example, each "person" in a university is an entity.
= Since we divide the tables into small pieces of tables that have the related data and since we want to retrieve data from multiple tables in one step, we need to relate these tables with each other. To do that We simply create logical relationships between these tables, or we now call them as entities.
Basically, we have the tables, which can be thought of as entities, and some columns have some keys or IDs to establish a connection, or called relations with some other tables. With these relations, we can easily get different data in one result by connecting these tables to each other while retrieving the data.
-
Starting from the Oracle database version 12c, Oracle has introduced the 'Pluggable Database' feature. So in the new versions, there is one big database, which is called container database, you can think that the root database, and inside of the container database, there are pluggable databases.
-
There must be at least one pluggable database inside the container database. And each pluggable database has the full attributes of a regular database. I mean, it has its own users, objects, tables, applications, etc. but the container database is not like the pluggable databases. It doesn't have any objects.
So why Oracle changed its database architecture to the pluggable database? = Most basically, in the previously released databases, I mean in 11g and earlier versions, each database should be installed on a separate server. But in companies, there are very small databases used for only some specific jobs. So there is no reason to dedicate another server for such a small database. Besides, each server is a lot of work for the DBAs.
-
So, to make that easier, Oracle updated its architecture, and starting from release 12c, now, there are pluggable databases in one container database.
-
So in this way, there can be many databases, I mean, pluggable databases for some specific tasks, in one big container database. In this way, the cost for the servers and the work for the DBAs also decrease.
Oracle database has many objects in it, and these objects can be categorized under two subjects: Schema objects and Non-schema objects.
-
A schema is a collection of logical structures of data or objects. And the schema objects are the logical structures created by users. A database user has only one schema with the same name as its user name.
-
Let's think about the HR schema for example. The user HR has a schema with the same name which is HR again. Every user has a schema with the same name and there are many schema objects created by the users, and I will mention the schema objects that you need to know throughout the course.
-
Tables:- A table is the base unit of the database to store data, formatted with columns and rows.
-
Views:- A view is a virtual table that provides access to a subset of columns or some restricted rows from one or more tables.
- Views are just SQL scripts with a name like a table and when you query from a view, the view executes that query and retrieves the data returning from that query. So views act like a real table but do not physically use any space for the returning data.
- Benefits of Views
Let's think that you have a query to generate the high salaries of each department, and you use that query so often in your work. It can be used individually, or you may need to use this view with other tables in your queries. If you create a view with this statement and name it "high_salaries" for example, now you can query from the high salaries view like a table. For example, select * from high_salaries. Just like a simple table. But this does not physically take any space in the database, because there is no data in it. This is just a stored SQL query.
-
Constraints:- Constraints are the rules for preventing or restricting invalid data entry into the tables.
-
Let's think that you have a column named "salary" on our table and you do not want any salaries greater than $50,000 in your table. And to do that, you can create a check constraint and prevent invalid data entry and provide data integrity by using such constraints. Or, if you want your column to have unique values, you can create a unique constraint for example. Constraints are really important.
-
Index:- Indexes are used for improving the speed of data retrieval from tables.
-
You can think of indexes just like indexes in the "books". In books, indexes show page numbers of the related topic and by checking the indexes, you open the related page directly without checking all the pages one by one.
-
Technically, an index knows the physical addresses of data in disks and goes to these blocks directly and this improves the retrieval of data very much in so many cases. The best part of indexes is they work automatically, so you don’t need to run them manually. Actually, you cannot run an index manually.
-
Sequence:- Sequences are the database objects which generate unique integers.
-
Generally used for primary key values, I mean, unique values for a row. And the main difference between the sequences from other databases is they can be used by multiple users or multiple tables basically.
-
Synonym:- A synonym is an alternative name or an alias for the database objects.
-
It basically references original objects. By using synonyms, you can query other users' objects without writing their schema names.
-
Let's say that the user John has created a table named employees. If you want to reach the employees' table from John's schema, you need to write "john.employees". If John creates a public synonym named employees, you don't need to write his schema name before the table name anymore. You can easily call the synonym name instead of the actual table name and there is no need to write his schema name anymore. So synonyms are references to the original objects.
-
Materialized View:- A materialized view is a logic beyond normal views. A materialized view has a real table filled by an SQL query, unlike the views. The real table is truncated and refilled with a specified time-frequency.
-
A materialized view is also a view, but there is a table with the same name and the table has real data in it. This is the main difference between the materialized views and the normal views.
-
Beneift of materialized views
-
Let's think that you have a view. And when you query from this view, assume that it returns the data in a very long time, because there might be a very costly SQL code in it like joining a lot of tables, making lots of sort operations, etc. So the response time for that query is very long. If you create a materialized view for this query, the database will fill its table with the query result, and when you query from the materialized view by its name, it will return the data from its table instead of running the query again and this will increase the performance so much in most cases.
-
Functions & Procedures:- Functions return some value, but procedures return nothing.
-
Actually, these are PL/SQL subjects. But these are also database objects. And I want to mention a little bit about them here because sometimes you will use some pre-built functions and procedures in your queries in this course. But first, I want to talk about what PL/SQL is for the ones who don’t know it yet.
-
PL/SQL is a procedural programming language that extends SQL capabilities. That means PL/SQL is SQL + Procedural Programming in Oracle database.
-
By using PL/SQL you can have variables, and control structures, which means structure, loops, triggers, I/O operations, and many others that you might be familiar with from another programming language like Java, .NET, etc.
-
If you know SQL and PL/SQL, you can perform a lot better operations on the Oracle Database and it might be very useful for your career. If you consider learning PL/SQL besides SQL, you can check my "The Complete PL/SQL Bootcamp" course. It explains all the PL/SQL subjects in detail.
-
So, in PL/SQL programming, we create functions and procedures for using some compiled code again and again without writing its code each time.
-
Imagine that you have a salary calculation code and it is very large and complicated. And you need to call this code a lot of times in your query, or in the other queries. In such cases, instead of writing this code over and over again, you can create a function or a procedure for that and call it wherever you need by just writing its name.
-
Besides, you can use SQL and PL/SQL code together in functions and procedures. The main difference between procedures and functions is, a function returns a value, but a procedure does not normally.
-
Triggers:- Triggers are compiled PL/SQL program units stored in the database and executed with a specific event like an insert, update, delete, or create, etc.
-
Triggers are used for performing other operations "BEFORE", "ON" or "AFTER" the main operation. Triggers are very important for business in real work, so I highly recommend you learn triggers as well.
-
Packages:- Packages are the schema objects that are compiled and stored in the database and consist of logically grouped SQL and PL&SQL codes, variables, cursors, etc. in it to perform one or more than one operation by using its functions or procedures.
-
Packages have functions and procedures and you use these functions or procedures to perform certain operations.
-
What is the difference of packages? = Packages can include many functions and procedures together and you can call and use any of them by writing "package_name.function_name" or "package_name.procedure_name".
-
Database Links
-
The database links are the connections between two physical database servers.
-
If you use more than one database in the same network, you can reach other database objects by using the database links because normally, you cannot access the objects of the other databases. But you can do that using the database links.
-
Basically, these are the most important database objects that you will mostly need to use in your business. Well, there are several other types of objects which are also stored in the database but are not placed in a schema.
-
Well, there are several other types of objects which are also stored in the database but are not placed in a schema. These objects are categorized under non-schema objects.
-
There are a lot of non-schema objects like directories, parameter files, roles, tablespaces, users, etc. The common feature of all these objects is they are not placed under a schema. And these are the topics that belong to the database administration except for roles.
-
Schemas are the collections of objects for each user in the Oracle database.
-
All the objects of a single user are collected under a logical set, schema.
-
Every user has objects under their schemas and a user does not have anything more than their schema has.
-
Besides, a user can have only one schema. So we simply call schemas as users.
-
HR, which is the abbreviation of Human Resources, is the schema that we will do our practices on, is also a user and a schema in our database which is created before by Oracle.
-
This schema is the most well-known and most commonly used test schema in Oracle Database training. Because this one is prepared with lots of data, just like how the companies do. Like the other schemas, this schema has some objects like tables, views, triggers, constraints, etc that we are going to use most of them in our lessons.
-
Of course, we will see almost all the objects in this schema in our course, but for now, the most important objects of it are the tables.
-
This kind of schema design is called as "entity-relationship" diagram. Shortly abbreviated as ER diagram.
-
This diagram shows the tables and the relationships between them.
-
Alright. We can say that Relational Database Management Systems(RDBMS) are formed of tables like these.
-
These tables are stored as tabular forms like Excel spreadsheets.
-
These capital texts are the names of the tables.
-
Tables are formed of columns and these lowercase names are the names of the columns of the tables.
-
Since this schema is human resources schema, all the tables in them are related to the employees. So the base table is employees. This table has the base information about the employees.
-
S'tructured 'Q'uery 'L'anguage.
-
Some people call it a sequel, too.
-
SQL is a language used to interact with the database.
-
SQL stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database or retrieve data from a database. Some common relational database management systems that use SQL are Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. -
Basically, it is a set of commands that we send to the database and database makes some operations based on that SQL code. So we write some codes, code means structured texts, and send that code to the database with some tools, then based on your code, the database makes some changes to some data or creates or deletes some objects like tables, users, etc, or grants or revokes some privileges, or bring you back some data etc.
-
Anything you need to do with the database is done with some SQL commands. So we can simply say that SQL is a language that we command to the databases and make some business.
-
You can solve pretty complex problems including a huge amount of data using simple SQL commands. So SQL is a really powerful language for data processing.
-
It is really easy and funny to learn. But it is really powerful for data manipulation.
-
We use SQL in so many places like Business Intelligence, Data Science, Database Administration, and Web Development.
-
Basically, for any of the jobs that you work with data, you will most probably use SQL.
-
Yes, Some of you may think about, if you can use the SQL that you learned in this course on other platforms like MYSQL, MSSQL, Postgres etc? Let me explain to you like that. Once so many databases started being created, they needed to make a standard.
-
So they created a standard for SQL and called them as "ANSI SQL" Standards.
-
Of course, each vendor can have some specific codes, but each database must execute any ANSI standard SQL. So when you learn how to code with SQL in this course, you can simply apply that standard SQL in other platforms. Besides these standard SQL codes, you will learn so many Oracle-specific codes that will step you ahead.
-
There are some tools to interact with the database like SQLPlus, SQL Developer, Toad, etc. We will mostly use SQL Developer, because Oracle itself creates it, and it is really easy to use, capable, and free.
-
But sometimes we will use sqlplus, too. Because it will also be installed by default and sometimes we may need that.
-
We simply type the SQL text into these tools and execute it within them.
-
These tools send that command to the database and if your code is syntactically correct, the database will execute your command and perform some business.
-
If your query wants to get some data, the database handles that data to the tool, and that tool will show us the data.
-
To sum up, I can say that, SQL is a structured text that we send to the database and make some operations on data.
- Install the latest Oracle Database from Oracle's official Website(Enterprise edition).
- Remember to note down the Pluggable database name while installing.
- For eg. Pluggable database name = XEPDB1.
- After Installing you need to unlock HR Schema.
- Code: -
sqlplus / as sysdba;
alter session set container=XEPDB1;
alter pluggable database open;
alter pluggable database XEPDB1 save state;
alter user hr identified by hr account unlock;
-
Run all lines 1 by 1 in cmd.
-
Last line won't work first so before that line Install Oracle SQL Developer from Oracle's official Website.
-
Open SQL Developer, Add a new connection. Name of a database, Username = sys, Password = Dhrupal@2418, Role - Sysdba, Click Service name radio button and write the pluggable database name - XEPDB1. And then Test & check if it is a success.
-
After success, go to cmd and
SQL> show con_name
SQL> connect / as sysdba
SQL> alter user sys identified by Test
run 1 by one and it will work.
- First create a new connection as sys as sysdba.
-
XEPDB1 is a pluggable database.
-
Create a user under that from other users - add user.
CREATE USER intro_user IDENTIFIED BY mypassword;
GRANT CONNECT TO intro_user;
GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO intro_user;
GRANT UNLIMITED TABLESPACE TO intro_user;
GRANT CREATE TABLE TO intro_user;
- Then create a new connection with this user-pass.
- Main database which we did with sys is a admin one so we can fetch all other users data in this.
- First database - sys, Users created - Test, Chiggy
- Second Database - Dhupu, Logged in with Chiggy, Table created- department
- If you wanted to check the department table of chiggy from sys database then query would be
select * from chiggy.department;
- Login into SQLPlus
- Open CMD
- sqlplus user/pass@pluggabledatabasename
- SQL>
- it should connect
- You can run all the queries which you had created in oracle. Just you have to run commit; query in SQL Developer after doing all changes like insert update etc. and if you created anything through CMD then after done run commit; query.
- The structured query language
allowsus to perform certain operations on the database. And we do these operations by performing basically these SQL statements. And these statements can be categorized into 4 types by their functionalities.
- DML- Data Manipulation Language - These commands are used to retrieve or manipulate data in our tables.
- Select- The SELECT is used to retrieve data from a table.
- Insert- The INSERT command is used to insert data into tables.
- Update- The UPDATE statement is used to modify the existing data within a table.
- DELETE- The DELETE deletes rows from a table.
- MERGE- The MERGE is a bit complicated in structure. It can perform inserts, updates, and deletes in one query.
- DDL- Data Definition Language - They are used to create, modify and drop any of the database objects.
-
CREATE- It is used to create a new table or other database objects like indexes, views, functions, procedures, triggers, etc.
-
ALTER- The ALTER command is used to modify the existing database objects. With the alter command, you change the structure of the database objects in many ways, after you create them.
-
Drop- The DROP is used to remove the objects from the database. If the object is removed, the data inside that object is also removed.
-
RENAME- The RENAME command is used to rename the database objects. You can change the name of the database objects with the RENAME command.
-
TRUNCATE- This removes all records from a table in one step.
-
If you noticed, we have DELETE, DROP, and TRUNCATE commands to remove the data from the database.
- DCL- Data Control Language - DCL statements are used to control privileges on the database. Whether you can access a database object, create a connection, etc. Oracle database has so many privileges. And if you don’t have any privilege, you cannot do anything. So you give or remove the privileges using these commands.
- So DCL includes two commands which are GRANT and REVOKE, that mainly deal with the privileges, permissions, and other controls on the database system.
- GRANT- GRANT is used to give a user the privilege to access any database objects or create sessions, etc.
- REVOKE- The REVOKE command withdraws the user’s access privileges given by using the GRANT command.
- TCL - Transaction Control Statements - TCL commands allow us to control and manage transactions to maintain the integrity of the data in the database.
- These commands are the COMMIT, ROLLBACK, and SAVEPOINT statements. You can think of these as saving or canceling the data manipulations.
- COMMIT- The COMMIT is used to make the changes permanent on the database.
- ROLLBACK- the ROLLBACK restores the database to the last committed state. Which basically means, taking the changes back.
- SAVEPOINT- The SAVEPOINT is used to temporarily save a transaction so that you can roll back to that point whenever you need.
-
We can store data in tables within the columns and these columns can store different types of data. Actually, one column cannot store more than one type of data. I mean, we specify certain data types for each column while creating a table and a specific column can store only its specified type of data.
-
VARCHAR2- Means variable-length character data. You can think of that as a data type storing strings.
-
Let me explain the variable length with an example. --> For example, if we create a column with a size of 100 characters, and if we add data in it with a length of 10 characters, this will use just 10 characters length space in the database. Not 100. Because of that, it is called variable-length character data.
-
So you should use the VARCHAR2 data type when you don't know the exact length of stored strings. Or if it varies from row to row.
-
So why do we specify the size then? --> It is because we need to specify the maximum length for that column. In this way, you can prevent adding bigger data into that column. It is for specifying an upper limit for the length of the data to be inserted.
-
What if we need to insert bigger data? --> In this case, you can modify that column and increase the size, or you can use another data type for that column. You will see the other types that you can use, soon.
-
CHAR- CHAR is similar to VARCHAR2, but with a difference. It is fixed-length character data, which means, if we create with a size of 100 characters, even if the value of our column has 10 characters, it will use 100 bytes of disk space in our database. So you can use CHAR when dealing with fixed-length strings.
-
For example, when you create a column to store social security numbers, you can use the CHAR data type since you will know in advance the exact length of the string you will be storing.
-
NUMBER- Numbers are variable-length numeric data, which stores numbers in it. So, let's continue with the NUMBER data type. Numbers are variable-length numeric data, which stores numbers in it.
-
We can store decimal numbers by writing their specifications here. P -> means 'precision, and S->means, 's'cale.
-
This data type can store fixed or floating point numbers up to 38 digits of precision.
-
DATE- As the name implies, this data type stores date and time values.
-
LONG- This data type is a variable-length character data type just like the VARCHAR2. But, this data type has a high capacity and it can store up to 2 gigabytes of data in it.
-
This datatype is used for backward compatibility. But, using a LONG data type is not recommended by Oracle anymore. Instead, the CLOB or BLOB data types should be preferred. RAW and LONG RAW types store raw-binary data in a column. This is also not recommended. Instead, you should use the other data types which are BLOB or BFILE.
-
BLOB- BLOB and CLOB data types are the sub-data types of LOB data type which stands for Large Objects.
-
The BLOB data type is mainly used to store and manipulate large blocks of unstructured data like images, videos, sound, and, any other multimedia files, etc. It can store up to 128 TB depending on your database block size.
-
Oracle prefers using BLOB or CBLOB data types instead of the LONG data type as it is more flexible than the LONG data type.
-
CLOB- CLOB is used to store large blocks of character data in the database. It is the same as the LONG data type. But, it has a higher capacity compared to the LONG data type. And it has a better algorithm which makes it much faster than the LONG data type in usage.
-
BFILE- The BFILE is also known as External Large Objects. It stores binary data in it, but in an external file in your operating system. Your BFILE column stores a pointer to the actual file in the operating system. And the size of a BFILE is limited by your operating system.
-
For example - With BFILE, you can store your pictures or other files. By the way, it is important to know that the BFILE data type is read-only, so you can't modify them.
-
ROWID- It is a base-64 number and represents the physical unique address of a row in a table. You can think that a record in a table is a row and each row must have a unique ID in the system and this ID is called ROWID.
-
The ROWID will remain the same until the record is deleted.
-
ROWID data type is stored in hexadecimal format. Therefore the hexadecimal string represents the unique address of a row in its table.
Outside Course
-
A data type is a classification of a particular type of information or data. Each value manipulated by Oracle has a data type. The data type of a value associates a fixed set of properties with the value. These properties cause Oracle to treat the values of one data type differently from the values of another.
-
The data types recognized by Oracle are:
-
ANSI-supported data types
{ CHARACTER [VARYING] (size)
| { CHAR | NCHAR } VARYING (size)
| VARCHAR (size)
| NATIONAL { CHARACTER | CHAR }
[VARYING] (size)
| { NUMERIC | DECIMAL | DEC }
[ (precision [, scale ]) ]
| { INTEGER | INT | SMALLINT }
| FLOAT [ (size) ]
| DOUBLE PRECISION
| REAL
}
- Oracle built-in data types
{ character_datatypes
| number_datatypes
| long_and_raw_datatypes
| datetime_datatypes
| large_object_datatypes
| rowid_datatypes
}
- This section describes the kinds of Oracle built-in data types.
- character_datatypes
{ CHAR [ (size [ BYTE | CHAR ]) ]
| VARCHAR2 (size [ BYTE | CHAR ])
| NCHAR [ (size) ]
| NVARCHAR2 (size)
}
- datetime_datatypes
{ DATE
| TIMESTAMP [ (fractional_seconds_precision) ]
[ WITH [ LOCAL ] TIME ZONE ]
| INTERVAL YEAR [ (year_precision) ] TO MONTH
| INTERVAL DAY [ (day_precision) ] TO SECOND
[ (fractional_seconds_precision) ]
}
- large_object_datatypes
{ BLOB | CLOB | NCLOB | BFILE }
- long_and_raw_datatypes
{ LONG | LONG RAW | RAW (size) }
- number_datatypes
{ NUMBER [ (precision [, scale ]) ]
| FLOAT [ (precision) ]
| BINARY_FLOAT
| BINARY_DOUBLE
}
- rowid_datatypes
{ ROWID | UROWID [ (size) ] }
-
The codes listed for the data types are used internally by Oracle Database. The data type code of a column or object attribute is returned by the
DUMPfunction. -
Table 6-1 Built-in Data Type Summary
- Oracle-supplied data types
{ any_types
| XML_types
| spatial_types
| media_types
}
-
This section shows the syntax for the Oracle-supplied data types.
-
any_types
{ SYS.AnyData | SYS.AnyType | SYS.AnyDataSet }
- spatial_types
{ SDO_Geometry | SDO_Topo_Geometry |SDO_GeoRaster }
- XML_types
{ XMLType | URIType }
- Converting to Oracle Data Types
- SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle data type name, records it as the name of the data type of the column, and then stores the column data in an Oracle data type based on the conversions shown in the following table.
- Notes
-
The NUMERIC and DECIMAL data types can specify only fixed-point numbers. For those data types, the scale (s) defaults to 0.
-
The FLOAT data type is a floating-point number with a binary precision b. The default precision for this data type is 126 binary, or 38 decimal.
-
The DOUBLE PRECISION data type is a floating-point number with binary precision 126.
-
The REAL data type is a floating-point number with a binary precision of 63, or 18 decimal.
-
Do not define columns with the following SQL/DS and DB2 data types, because they have no corresponding Oracle data type:
-
GRAPHIC
-
LONG VARGRAPHIC
-
VARGRAPHIC
-
TIME
-
Note that data of type TIME can also be expressed as Oracle datetime data.
-
User-defined data types
-
User-defined data types use Oracle built-in data types and other user-defined data types to model the structure and behavior of data in applications.
Finish
-
NULL means unknown or nonexistent.
-
In the database context, Null is a special marker used in the Structured Query Language to indicate that the data does not exist in the database.
-
NULL is a reserved word used to identify this marker.
-
So, what do I mean by that**?** Even though we generally call it a NULL value, actually, NULL is not a value. It is just a state indicating that an item's value is unknown or does not exist.
-
Some people may confuse about the NULL with a space, or 0, or an empty string, etc. But in fact, it is not 0 or a blank or an “empty string” So do not think of NULL values similar to 0 or blank values, etc. It is not the same. It does not behave like any of these values.
-
So let me show you how you will see the null values on the database. This is a sample representation of a table. You will see the table data as shown here usually.
-
As can be seen from the output, there are some nulls in some cells of this table.
-
So, what do these NULLs mean here? If I talk about the commission percentage column, it means, these employees do not have commission percentage values. There are no data in these cells. And we call this nonexistence as NULL. But be careful, it is not zero. You can see zero values here as well. Because these columns have the data and they are zero.
-
So basically, if a field has no data in it, instead of saying "There is nothing in that field", we basically say, "There is a NULL value in that field".
-
By the way, do not confuse about that, the NULL texts within the parentheses in these cells here are not the values of these cells. These are just the representations of the null values for us to understand that there is no value here.
-
If I talk about the manager_id column, as you can see, all the rows except for Steven King have manager_id values. But this column doesn’t have to have a value. Because he is the CEO of this company, so there is no one above him hierarchically. And his manager_id value has no value. To represent this, we say, "That value is NULL".
-
And just to make it clearer, I changed some values here. For example, there is a space in the email column here. If it shows a space, be careful that it means, it is not a null value. There is a value that includes a space here.
-
However, it writes null here in the phone_number column. So there is not any data here. It is just null.
-
So, to make you understand better, I think you need to know the storage of the NULL values in the database. If the value is 0, it takes up some space in the database disks.
-
Similarly, the spaces and empty strings also take up some space in the disks.
-
However, null values do not use any space in the disks physically. Because of that, we can say it doesn't exist or it is unknown to the database.
-
Actually, the null values are really important because they work differently than the other values. And if you don’t know that, you may have some problems.
-
Just for a simple example, if you want to count the number of rows in a table, null values are not counted. Because the database thinks it is not a value. However, zeros, spaces, and empty strings are counted like the other values. So understanding that null actually doesn't exist is important.
-
We know that, in the databases, we store our data in tables. And the tables consist of columns. And each column has some attributes. So to see the attributes of the columns of a table, we use the DESCRIBE command.
-
As the name implies, the DESCRIBE command is used to view the definition of a table or a view, a procedure, etc. But, since we store the data in tables, we often use DESCRIBE command to describe the structure of a table.
-
When you use the DESCRIBE command, it displays the names of the columns, whether the columns are nullable or not, which means, the columns can contain NULL values or not, and the datatype definitions of these columns.
-
--> So, how can you use that?
-
--> You just write the command DESCRIBE, or DESC which is the abbreviation of DESCRIBE, followed by the table name.
-
For EG: You know that we have a table named "employees". And let’s say that we want to view the structure of the employee's table. For example, what are the columns of this table and the data types of these columns?
-
In this kind of situation, we use the DESC or DESCRIBE commands. We just write DESCRIBE and our table name which is employees.
-
So let's run it.
-
We can see that these are the names of the columns. All the columns of the employee's table. And you see that some of these columns can have NULL values, and some of them cannot. NOT NULL which are going to see in further lectures, means, you cannot insert NULL values into this column. And these are the data types of the columns. This column is in the number data type and this is in varchar2 and date, and others if there are.
-
Now I want to show you an easier way of seeing the attributes of a table.
-
You can get a more detailed explanation of a table by using the SQL Developer tool. To do that, you need to select the table name here.
-
You can do that by double-clicking on it or you can select it normally. Then, you should press the Shift and F4 buttons together.
-
As you can see, SQL Developer shows us a more detailed explanation of the employee's table. I personally use that a lot more than the describe command. Because this one is easier to reach and you can see many more attributes of the table and the other objects connected to your table like constraints, triggers, indexes, etc.
-
The INFO command, which is an improved version of the DESC command.
-
As you know, to check the structure of a database object, -mostly a table-, we just write the command desc or describe, followed by the table name. So let's write employees and run it. You see that, the column list of the specified table is printed. Whether they are nullable or not, and their datatypes.
-
For example, we have an employee_id column here and it is a not null column. This means it has to have a value for each row. And we know that it can’t have a value greater than six digits. But, I don’t know if that’s a primary key or not (which we are going to see in further lectures), but basically, a primary key is a key in a relational database that is unique for each row. And also neither I don’t see any column comments or table comments which are used to help other developers to know the purpose of the columns or tables. And I don’t know when the table was created, etc. So we cannot see this kind of extra information that we may need while coding, and we cannot see them by using the desc command. So, now let’s look at how to use the INFO command then.
-
I write information and then write the table name, employees. When I run that, as you can see, we get a lot more information here.
- We also have the columns list of the employees table. The first thing you’ll notice here is the employee_id column has an asterisk. That means, this is the primary key column of that table. Then, we also have the data type information of the columns, and its nullability. Also we have default values attached to these columns. That means, while creating the table, if we provide a default value for that column, when we insert a new record without specifying a value for that column, the default value will be inserted instead of a NULL value.
-
And then below that you see the list of indexes. You are also gonna learn what the indexes are but basically, indexes are used to speed up our query response times. You are also gonna learn what the indexes are but basically, indexes are used to speed up our query response times. And below that, you see a list of foreign key constraints. You will see these later, but basically, they show the connections of the other tables to the employees table by their constraints. This is sometimes really important. Because sometimes while you are trying to insert, update or delete something from the employees table, you may have some troubles because of these constraints. And in this case, this information can be very crucial.
-
So, let’s make one more example. As I said before, we can shorten the information command as info as well, just as we did in the describe command. As I said before, we can shorten the information command as info as well, just as we did in the describe command. So let’s do this. I write the command info. INFO Department; and when I run that, it'll work the same as the INFORMATION command.
-
And lastly, I want to mention that, we have a little bit different usage of the "info" command. This is the info+ command which allows us to see some more column statistics. So, let’s look at info+ plus for the employee's table.
-
If I run that, as you can see, instead of getting column comments, now we’re getting some useful column statistics.
-
So, let me explain these new stats basically. "Low value" means the smallest value of that column in that table. If the value is a number, you see the smallest integer for that column,
-
If the value is a string, then you'll see a value that comes first alphabetically. High value means the greatest value for that column. If the value is a number, you see the greatest integer for that column. If the value is a string, then you'll see a value that comes last alphabetically.
-
The next column is, histogram. A histogram simply means a special type of column statistics that provide more detailed information about the data distribution in a table column.
-
Code
DESC employees;
INFORMATION employees;
INFO departments;
INFO+ employees;
Select * from employee;
Select * from employee
/
--Retrieving all the columns(fields) from a table.
SELECT * FROM employees;
--Retrieving specific column(s) from a table.
SELECT first_name, last_name, email FROM EMPLOYEES;
- If we want to use space or special characters in alias name when it should be done into inverted commas"".
SELECT first_name, last_name, email FROM employees;
SELECT first_name AS name, last_name as surname, email FROM employees;
SELECT first_name AS "My Name", email "E-mail" FROM employees;
SELECT first_name AS "My Name", email "E-mail" FROM employees;
SELECT employee_id, salary + nvl(salary*commission_pct,0) + 1000 new_salary, salary FROM employees;
- There is a table called dual in the Oracle Database. Sometimes, we may need to use this table in some specific situations.
- Let's query from this table first. I write
select * from dual; - And if I run that, you see that there is only one column and one row in this table.
- If so, why do we use the dual table?
It's a sort of dummy table with a single row used for selecting something when you're not actually interested in the data, but instead, want to show the results of some functions, etc. As I said, it is generally used by developers to show or explain something.
-
Let me show you this through some examples.
-
Sometimes, we may need to get specific texts as the output. Let's write a query including a text then. I write select for example
select My Name is Adam from dual;
-
You see that we have an error. So, how can I print this string in the output? If I enclose this text with the single quote operators and run that query again.
select 'My Name is Adam' as "Output" from dual;
- The quotation mark is used to increase readability and usability. 'q' is used when we have special characters in it.
- For example:-
- In the above eg, we've used ' this character and run it. error shows coz it has a special character so need to use 'q'.
- As you can see, we get an error. The query cannot be executed. Because the single quote has a special meaning in SQL. The Oracle Database server thinks that the characters of the text are starting from here and ends in here. the rest of them are not interpreted as a part of the text. Because these are not enclosed with the single quote characters. The compiler thinks that these are the SQL-reserved words like select, where, etc. And since these are not reserved words, we get an error.
- So, how can I handle that?
- If I want to write a special character like a single quote within a text in an SQL statement, then, I need to write another single quote as an escape character.
select 'I''m using quote operator in SQL statements' as "Output" from dual; - In this way, I say to the compiler that, the next character after the single quote character which is inside of my text is not an escape character. It’s a part of my text. And assume that if I need to write single quote characters one hundred times in a text, I need to write one more single quote for each one of them. But basically, I can write the same text easier by using the quote operator.
- As you can see if we use 'q' then it works properly.
- Here is how quotation marks are used in SQL statements. The letter q, in upper or lower case, doesn't matter followed by a single quote character and followed by a left square bracket. These three characters must be in this order.
- we can use all these characters, or even any letter, or asterisk, etc. So it doesn’t need to be a square bracket. It can be anything else. But we generally use the square brackets among the developers because it is easier to understand and like a standard. But these usages having asterisk characters as delimiters, or a letter, which is b here, are completely valid. So, let me show you some examples of that. I have used the square brackets as delimiters here.
- But I can change these brackets with something like <>, any alphabet etc.
- But, bear in mind that it needs to start and end with the same character if you use a letter as delimiters.
select * from dual;
select 'My Name is Adam' as "Output" from dual;
select 'I''m using quote operator in SQL statements' as "Output" from dual;
select q'[I'm using quote operator in SQL statements]' as "Quote Operator" from dual;
select q'<I'm using quote operator in SQL statements>' as "Quote Operator" from dual;
select q'dI'm using quote operator in SQL statementsd' as "Quote Operator" from dual;
- The UNIQUE and DISTINCT keywords are completely the same. So what is the difference between them? The UNIQUE is there Oracle's old SQL syntax and still supported. It is basically the synonym of DISTINCT. Besides, the DISTINCT command belongs to the standard SQL, and the SELECT UNIQUE is non-standard. I mean, in other databases UNIQUE keyword may not be recognized at all. For this reason, I recommend you to use the DISTINCT keyword.
- There are some invalid usages of the DISTINCT operator. So what are they? The DISTINCT operator is a row-based operator. It means, only one distinct operator is used in a SELECT statement.
SELECT first_name FROM employees;
SELECT distinct first_name FROM employees;
SELECT unique first_name FROM employees;
SELECT distinct job_id, distinct department_id FROM employees; **Error**
SELECT distinct job_id, department_id FROM employees;
SELECT distinct job_id FROM employees;
SELECT distinct job_id, department_id, first_name FROM employees;
SELECT job_id, distinct department_id, first_name FROM employees; **Error**
- If we want to check the total number of rows fetch after run the query then go to output in Query Result then right click on that and select count rows.
- It mainly used when we have more than 50 rows coz output shows first 50. To see all rows we need to drag till down at last to check total number of records. Instead you can check with count rows.
SELECT 'My Name is Alex' FROM employees;
SELECT 'My Name is ' || first_name FROM employees;
SELECT 'The commission percentage is ' || commission_pct AS concatenation,commission_pct FROM employees;
SELECT first_name || ' ' || last_name AS "full name" FROM employees;
SELECT * FROM employees;
SELECT * FROM locations;
SELECT street_address || ',' || city || ',' || postal_code || ',' || state_province || ',' || country_id AS "full address"
FROM locations;
-
Any calculation performed with the NULL value returns NULL. I can say that in almost all operations except for the concatenation performed by the null values will return null.
-
It's so likely that your data will often contain some null values, and it needs to be handled. And null values can often lead to mistakes in your calculations, and as a result, learning how to deal with the Null values is critically important.
-
For this reason, you are going to see how to handle the NULL values by using some SQL functions in further lectures. But for now, it’s enough to know that any calculation performed by the NULL value returns NULL.
-
When I query the employees table select * from employees. You see that the majority of the commission percentages are null in our table. So, let's write salary comma, salary is multiplied by commission percentage and run it.
SELECT salary, salary*manager_ID, manager_ID FROM employee;
- As you can see , if we do any arithmetic operation with a column which is NULL shows null it will show null if we do any arithmetic operation.
SELECT * FROM employees;
SELECT employee_id, salary, salary*12 as annual_salary FROM employees;
SELECT employee_id, salary, salary+100*12 as annual_salary FROM employees; --> as BODMAS - it will first multiply and then add
SELECT employee_id, salary, (salary+100)*12 as annual_salary FROM employees; -->so add parentheses() so it will count that first.
SELECT sysdate FROM dual; --> sysdate = current date
SELECT sysdate + 4 FROM dual;
SELECT employee_id, hire_date, hire_date+5 FROM employees;
SELECT salary, salary*commission_pct, commission_pct FROM employees;
-
Query
-
Write a SQL Query to Calculate New Salaries Assume that the company you work in wants to increase the salaries by 20% this year, and also wants to add 1000 as bonus to each salary after 20% increase. Based on this information, write a SQL query that returns the new salaries from the EMPLOYEES table.
-
Your query should return the employee_id, salary and new_salary columns.(new_salary should be the alias for the calculated salary)
SELECT employee_id, salary, salary * 1.2 + 1000 AS new_salary FROM employees;
1 is equal to a whole.
Image 1 as equal to 100%. Now as a decimal, 20% would be 0.2 so when you add 20% to something you multiply by 1.2 as you then have that 1 whole plus the 20%.
I hope that made sense I might have just made it more confusing. 1.2 is 120%, 0.2 is 20% and 1 is 100%
SELECT * FROM employees;
SELECT * FROM employees WHERE salary > 10000;
SELECT * FROM employees WHERE job_id = 'IT_PROG';
SELECT * FROM employees;
SELECT * FROM employees WHERE salary > 10000;
SELECT * FROM employees WHERE salary < 10000;
SELECT * FROM employees WHERE hire_date > '01-JUN-05';
SELECT * FROM employees WHERE hire_date = '21-MAY-07';
SELECT * FROM employees WHERE manager_id = 100;
- The date values must be enclosed within the single quotes.
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 14000;
SELECT * FROM employees WHERE hire_date BETWEEN '07-JUN-02' AND '29-JAN-08';
SELECT * FROM employees WHERE hire_date BETWEEN '07-JUN-02' AND '29-JAN-05';
-
You can use up to 1000 values in the IN list, but actually, it is not recommended for the performance aspect. You can have better information in my SQL Tuning course about the IN clause performance.
-
The first names are not ordered this time. So why's that? Actually, this is very advanced for you for now. But I want to mention basically here. You will learn the "Indexes" in this course. They are like the indexes of the books. And the indexes store the data in order. So if the column you use in the IN clause has an index on it, you will see the data are ordered alphabetically, numerically, etc., depending on the data type of that column. If not, it will return in the insertion order.
SELECT * FROM employees
WHERE first_name IN ('Steven', 'Peter', 'Adam');
- In alphabet, it wont come in order, it'll show how we wrote like Steven, Peter, Adam.
SELECT * FROM employees
WHERE employee_id IN (50, 100, 65, 210) --> We have mentioned number unordered but in result it'll show sort like 50, 65, 100, 210
SELECT * FROM employees
WHERE employee_id IN (50, 100, 65, 210, 150);
SELECT * FROM employees
WHERE first_name IN ('Steven', 'Peter', 'Adam'); --> In alphabet, it want come in order, it'll show how we wrote like Steven, Peter, Adam.
SELECT * FROM employees
WHERE first_name IN ('Steven', 'Peter', 'Adam', 'aa');
SELECT * FROM employees
WHERE hire_date IN ('08-MAR-08', '30-JAN-05');
- When you do not know the exact value to search for, or you want to get the values which include your search text, you can use the LIKE operators to perform searches on similar values in strings. The similar values are given by a pattern that has to be matched. The pattern matching operation is referred to as a wildcard search.
- The strings are case-sensitive.
SELECT * FROM employees;
SELECT * FROM employees WHERE job_id = 'SA_REP';
SELECT * FROM employees WHERE job_id LIKE 'SA_REP'; --> without Wildcard Operator
SELECT * FROM employees WHERE job_id LIKE 'SA%';
SELECT * FROM employees WHERE first_name LIKE 'A%';
SELECT * FROM employees WHERE first_name LIKE '%A';
SELECT * FROM employees WHERE first_name LIKE '%a';
SELECT * FROM employees WHERE first_name LIKE '%a%';
SELECT * FROM employees WHERE first_name LIKE '_r%';
-
NULL Operator means Unassigned, Unknown, Unavailable.
-
And while working with the databases, most probably your data will often contain some null values. So in this kind of situation, you may want to perform a search for the null values.
-
At this point, you can not perform a search by writing where column name is equal to null.It will return nothing.
-
And you should be aware of the null value searches because you may get some unintentional results, especially when you learn the subqueries. Knowing this will be more important.
SELECT * FROM employees WHERE commission_pct = NULL;
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE commission_pct IS NOT NULL;
- AND
- OR
- NOT
- NOT operator is used when some specific we don't want. Like we want the data of all emp except with the name Alex.
SELECT * FROM EMPLOYEES WHERE first_name NOT IN ('Alex');
SELECT * FROM employees WHERE job_id = 'SA_REP' OR salary > 10000;
SELECT * FROM EMPLOYEES WHERE salary > 10000 AND job_id IN ('SA_MAN', 'SA_REP');
SELECT * FROM EMPLOYEES WHERE salary > 10000 AND job_id NOT IN ('SA_MAN', 'SA_REP');
SELECT first_name, last_name, job_id, salary FROM employees
WHERE (job_id = 'IT_PROG' or job_id = 'ST_CLERK') and salary > 5000;
SELECT first_name, last_name, job_id, salary FROM employees
WHERE job_id = 'IT_PROG' or (job_id = 'ST_CLERK' and salary > 5000);
SELECT first_name, last_name, job_id, salary FROM employees
WHERE job_id = 'IT_PROG' or job_id = 'ST_CLERK' and salary > 5000;
SELECT first_name, last_name, department_id, salary
FROM employees
WHERE salary > 10000 AND department_id = 20 OR department_id = 30;
SELECT first_name, last_name, department_id, salary
FROM employees
WHERE salary > 10000 AND (department_id = 20 OR department_id = 30);
SELECT * FROM employees;
SELECT first_name, last_name, salary FROM employees ORDER BY first_name;
SELECT first_name, last_name, salary FROM employees ORDER BY last_name;
SELECT first_name, last_name, salary, (10*(salary/5) + 3000) - 100 NEW_SALARY
FROM employees ORDER BY NEW_SALARY;
--> As we added new column of arithmetic operation and column name is new_salary so we can directly write that in order clause
SELECT first_name, last_name, salary, (10*(salary/5) + 3000) - 100 NEW_SALARY
FROM employees ORDER BY 1; --> 1 means it'll show 1st column after SELECT written.
SELECT first_name, last_name, salary, (10*(salary/5) + 3000) - 100 NEW_SALARY
FROM employees ORDER BY 2;
SELECT *
FROM employees ORDER BY 2; --> Second column of the table.
SELECT *
FROM employees ORDER BY 5;
SELECT *
FROM employees ORDER BY first_name, last_name;
SELECT *
FROM employees ORDER BY first_name, job_id, salary;
select employee_id, first_name, last_name, salary from employees order by first_name;
select employee_id, first_name, last_name, salary from employees order by first_name asc;
select employee_id, first_name, last_name, salary from employees order by first_name desc;
select employee_id, first_name, last_name, salary from employees order by first_name desc, last_name;
select employee_id, first_name, last_name, salary from employees order by first_name desc, last_name desc;
select employee_id, first_name, last_name, salary from employees order by first_name desc, salary desc;
select employee_id, first_name, last_name, salary s from employees order by first_name desc, s desc;
select employee_id, first_name, last_name, salary s from employees order by 2 desc, s desc;
select first_name, salary, commission_pct from employees order by commission_pct;
select first_name, salary, commission_pct from employees order by commission_pct;
select first_name, salary, commission_pct from employees order by commission_pct NULLS FIRST;
select first_name, salary, commission_pct from employees order by commission_pct ASC NULLS FIRST;
select first_name, salary, commission_pct from employees order by commission_pct DESC;
select first_name, salary, commission_pct from employees order by commission_pct DESC NULLS LAST;
SELECT employee_id, first_name, last_name, salary, rowid, rownum from employees;
SELECT employee_id, first_name, last_name, salary, rowid, rownum from employees where department_id = 60;
SELECT employee_id, first_name, last_name, salary, rowid, rownum from employees where department_id = 80;
SELECT employee_id, first_name, last_name, salary, rowid, rownum from employees
WHERE department_id = 80 and rownum <= 5 order by salary desc;
-
Query
-
After made changes in query
-
Rowid is permanent & rownum is temporary. You can see the below example where it clearly shows the difference. if we change the query, rownum differs but rowid is the same for that particular one.
-
However, since the rownum is generated for the returning query results, the rownum of a row can change when the output changes.
-
So where can we use them? As I said before, we can use the rownum pseudocolumn to limit the returning rows for example.
-
Query - If your manager says to create a report of first 10 employees whose salary is more than 45000.
select first_name, salary, rowid, rownum from employee where salary > 45000;
select first_name, salary, rowid, rownum from employee where salary > 45000 and rownum <=10;
- Another query, if manager wants the top 5 name of employee who earns more with department id = 2.
- So first I check the employee who is in deptid = 2.
- Then I applied the top 5 with high salary
-
As you can see and match both queries, in second one the highest salary shown is 92000 but in first query we can see that it has more highest salary than 92000. as you can check 7. Cynthia with 118000 salary.
-
It happened coz it is considering rownum first then others so for that :- we need to use parenthesis. Above query is called subquery.
-
If we want top 6 employee list who is having highest salary
-
Above is the list of all employees with highest salary. So from this we only need top 6. But the first name - Pamela is a manager so we don't need that. we need 5 excluding manager for that we need to use fetch.
1. select first_name, salary
from employee
order by salary desc nulls last
offset 1 row fetch first 6 rows only; --> if exclude 5 rows then offset 5 row
- As you can see, we got top 6 excluding pamela.
- If we use WITH TIES IN PLACE OF ONLY then
2. select first_name, salary
from employee
order by salary desc nulls last
offset 1 row fetch first 6 rows with ties;
- Check both the queries1 and 2. See the difference. In ONLY it'll show total 6 values & in WITH TIES it'll show the last same salary of all emp.
SELECT first_name, last_name, salary FROM employees
ORDER BY salary DESC
OFFSET 1 ROW FETCH FIRST 10 ROWS ONLY;
SELECT first_name, last_name, salary FROM employees
ORDER BY salary DESC
OFFSET 1 ROW FETCH FIRST 10 ROWS WITH TIES;
SELECT first_name, last_name, salary FROM employees
OFFSET 1 ROW FETCH FIRST 10 ROWS WITH TIES;
SELECT first_name, last_name, salary FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS WITH TIES;
SELECT first_name, last_name, salary FROM employees
ORDER BY salary DESC
OFFSET 5 ROW;
SELECT first_name, last_name, salary FROM employees
ORDER BY salary DESC
OFFSET 1 ROWS FETCH FIRST 10 ROWS WITH TIES;
SELECT first_name, last_name, salary FROM employees
ORDER BY salary DESC
OFFSET 1 ROWS FETCH FIRST 10 ROWS WITH TIES;
- It is used when we want to check multiple times. EG:- I want to check the values department wise. So query would be
select first_name, last_name, department_id from employee where department_id=30; - And so on for other dept id like 40, 50 and etc. But for that , everytime we need to make changes in query. To make work easy we can use substitution variable.
select first_name, last_name, department_id from employee where department_id=&deptno;
SELECT employee_id, first_name, last_name, department_id
FROM employees WHERE department_id = 30;
SELECT employee_id, first_name, last_name, department_id
FROM employees WHERE department_id = &department_no;
SELECT employee_id, first_name, last_name, department_id
FROM employees WHERE first_name = '&name'; --> to be written in single quoted
SELECT employee_id, first_name, last_name, department_id
FROM employees WHERE first_name = &name; --> if not written in single quoted then, the value of dialog box to be quoted.
SELECT employee_id, first_name, last_name, &column_name
FROM &table_name --> Can mention any table
WHERE &condition --> any condition like salary >1000
ORDER BY &order_by_clause;
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN &sal AND &sal + 1000; -->
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN &&sal AND &sal + 1000;
- Difference between &sal AND &sal ----- &&sal AND &sal.
- &sal - is a substitution variable as we saw in previous topic.
- But when we want to use same again more then 1 time, so we write &sal AND &sal. So it'll ask 2 times dialog box coz that variable doesn't store the value.
- To make it simple, if we write &&sal AND &sal, then it'll ask once and consider that value for other same variable and store it.
- Since we used a substitution variable with the double ampersand, this value will be stored throghout your session.
EG:- select &&columname from employee order by &columname; --> run query and mention column name **Departmentid**
select furst_name, &&columnname from employee where salary >1000 order by &columname; --> run query - now in this it won't ask for columname coz the value is already stored when I ran above query.
SELECT employee_id, first_name, last_name, &&column_name
FROM employees
ORDER BY &column_name;
SELECT &&column_name
FROM employees
GROUP BY &column_name
ORDER BY &column_name;
- As I said, once we run and put value, it'll store still the session runs. What if we want to change the value? We need to disconnect the database and connect again.
- To make it simple, we'll use define keyword.
Define sal = 1000;
select &salary, first_name from employee;
run query = if wont show dialog box and shows where sal is 1000
undefine sal;
and then run that query again then it'll show dialog.
DEFINE emp_num = 100;
SELECT * FROM employees WHERE employee_id = &emp_num;
DEFINE emp_num = 200;
DEFINE column_name = 'first_name';
UNDEFINE emp_num;
DEFINE;
DEFINE column_name;
UNDEF column_name;
DEF column_name;
- you can use DEF and UNDEF instead of DEFINE and UNDEFINE commandsvlike the DESC instead of DESCRIBE command.
ACCEPT emp_id PROMPT 'Please Enter a valid Employee ID:';
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = &emp_id; --> stores until session ends so must undefine
UNDEFINE emp_id;
ACCEPT min_salary PROMPT 'Please specify the MINIMUM salary:'
ACCEPT max_salary PROMPT 'Please specify the MAXIMUM salary:'
SELECT employee_id, last_name, salary
FROM employees
WHERE salary BETWEEN &min_salary AND &max_salary;
UNDEFINE min_sal;
UNDEF max_sal;
- First query - set verify on; run it. then select query - select first_name, last_name, department_id from employee where department_id=&deptno; and don't run it but select that query and f5 (Run Script). dialog box will open and put your value and enter it'll show as which value we put.
- if we run set verify off and then f5 that query then put value in dialog box but will show nothing.
SELECT employee_id, first_name, last_name, department_id
FROM employees WHERE department_id = &dept_id;
SET VERIFY ON;
SET VERIFY OFF;
SELECT * FROM departments WHERE department_name = 'R&D';
SET DEFINE OFF;
SET DEFINE ON;
-
I want to mention something really important about substitution variables.
-
Suppose that we have a department named R&D which stands for Research and Development departments in the departments table?
-
So I write SELECT * FROM departments WHERE department_name=‘R&D’;
-
So, if I try to execute this query, a prompt window appeared asking me to enter a value for D. But, this not what I wanted.
-
I don’t mean that this is a substitution variable. This is the normal character ampersand in the alphabet.I just want to get this department right? Although, this is not meant to be a substitution variable the SQL Developer or SQL Plus think that this is a substitution variable. So even if you type a valid department name here, since it will add the R here to the beginning of the text, it will not work But that is not our point actually. Now I want to get the department R&D and I cannot do that.So, is there a way to get rid of this confusion? Yes, there is.
-
In these kinds of situations, Oracle allows us to use the "SET DEFINE OFF command" to inform the SQL Developer or SQL*Plus that I will not use a substitution variable in this session anymore.
-
I write SET DEFINE OFF. And execute it. Great. Now let’s execute our query again.
-
As you can see, in this way, the SQL Developer used the ampersand character as a normal character, instead of a substitution variable identifier. And the prompt window didn’t appear and our query worked successfully.
-
The functions like some little machines that run specific tasks in a factory. You put something in a machine, then press the start button. It does the job and returns something out. Just like these machines, the functions have pretty similar usages.
-
In SQL programming, in simple terms, a function is a subprogram that is used to return a single value. And Oracle provides so many built in functions that are incredibly useful to manipulate the data values.
-
They allow us to reuse the code any time we want by just calling the function name instead of writing the whole code over and over again. So that simplifies our programs.
-
if we need to use a particular code once or twice, then there is no need to create a function for that. But if we need to use that specific code several times and for the same purpose, we can create a function for that and reuse it wherever and whenever we need.
-
When you create a function, it is compiled and stored in the database. So because of that, it is also called as stored functions.
- The single row functions, as the name implies, gets only one row as input and return only one result as output.
- On the other hand, multiple row functions, which can also be called as group functions get more than one row as input and return one result as output for that group of rows. One result for multiple rows.
-
Nested means using a function inside of a function. The output of the inner function will be the input for the outer function. This is frequently used amongst the developers, so please listen to me carefully. I write more than one function and one is inside of the other one. The nested functions are evaluated from the most inner function to the outermost one.
-
The returning data type can be different than the input types. That means, for example, I can use a string as the input value, but the output of the function can be a number or something else. It can be the same as well. But bear in mind that the type of the output needs to be certain. I mean, if you created the function to return a number value, it will definitely return a number value. It cannot return a string or another data type. The same goes for the input parameters as well. If the input parameter is a number data type, you can not pass in a data value for that parameter. You will understand better when you see the examples.
SELECT first_name, UPPER(first_name),
last_name, LOWER(last_name),
email, INITCAP(email) FROM employees;
SELECT first_name, UPPER(first_name),
last_name, LOWER(last_name),
email, INITCAP(email) FROM employees
WHERE job_id = 'IT_PROG';
SELECT first_name, UPPER(first_name),
last_name, LOWER(last_name),
email, INITCAP(email),
UPPER('bmw i8')FROM employees
WHERE job_id = 'IT_PROG';
- If I wanted to find some name with particular surname like 'King'.
Query - Select * from employee where last_name='KING'
Result - No rows
Query - Select * from employee where last_name='king'
Result - No rows
-
No rows selected because character matching is case sensitive in Oracle and we cannot get the related rows unless they have an exact match. Actually, this is a common problem in real business. Because most of the time you will need to query from an unstructured data.
-
For example, some users will type in their names in uppercase, some in lowercase or some maybe in initcap, or maybe if they have multiple names. The combination of all. To be clearer, even to users, having the same last name such as King can write their names differently. But that doesn't change the fact that their last names are king.
-
So when your manager asks you to get the users whose last names are king, you need to find a way to get them both. So how can we handle that problem?
-
The answer is using the case conversion functions, of course. So how do we do that? Actually, it is quite simple.
-
If I enclose the last name column with the lower function because we typed our text in lower case on the other side of the comparison. Same with upper and initcap.
SELECT * FROM employees
WHERE last_name = 'KING';
SELECT * FROM employees
WHERE last_name = 'king';
SELECT * FROM employees
WHERE LOWER(last_name) = 'king'; --> first converted whole text in lower and then mentioned in lower to find.
SELECT * FROM employees
WHERE UPPER(last_name) = 'KING';
SELECT * FROM employees
WHERE INITCAP(last_name) = 'King';
-
The character manipulation functions are used to extract change format or alter a character string. One or more characters or words are parsed into a character manipulation function, and the function will then perform its functionality on the input strings and return the changed, extracted, countered or altered value.
-
The Substr function
Syntax- SUBSTR(source_string, position[,length])
Example- SUBSTR('Sql Course',1,3)
Result- Sql
Substr stands for substring.
This function extracts a substring of a determined length from a string.
We pass in a source string into this function as the first parameter.
As you know from the previous lessons, I said that the single row functions may take in more than one parameter.
So I passed in the course text as the first parameter to this function.
This is the source string that the substring will be taken from.
And the second parameter is the starting position.
It means the character position in the source string where you want to start extracting.
By the way, please keep in mind that the first position starts from one, not zero as in many other programming languages.
I mean, unlike some other programming languages, the index of the first character is one in SQL, not zero.
And the third parameter is the substring length parameter.
The length parameter is optional, as you can see, and it specifies how many characters to be extracted from the source string.
So for the first example, let's say we want to extract a new substring starting from the first character,
which is s and ends when itrillioneached to the third character, which is L.
So first I write base or source string, and then I pass in the starting position and after that I pass in the length parameter.
If the length parameter is not specified, then the substring will return everything from the starting
position to the end of the string by default.
In our case, I want to extract three characters starting from the first character of the source string.
So if I run that, the result will be SQL here.
This is how we use the Substr function.
By the way, do not confuse with the length parameter here.
It means the number of characters to be extracted.
That doesn't mean finishing position for extraction.
I mean, if the starting position would be five and the length would be two.
Let's count it quickly.
One, two, three, four, five.
The fifth character.
In other words, the starting position is C here.
By the way, do not forget that the spaces are also characters, which means they are also counted.
So after the fifth character, which is See here, got me two characters, including the fifth one.
So this function extracts the characters C and O.
I mean the extracted substring will be co.
If value is Dhrupal. Query- SUBSTR('Dhrupal',1,3) = Dhr
SUBSTR('Dhrupal',2,5) = hrupa --> 2 means from which letter you want and 5 means total letters.
SUBSTR('Dhrupal',2) = hrupal
- If the string is not long enough, it returns only the existing ones. If there are more characters than the specified length, then it returns only as much as the specified length.
SUBSTR('Dhupu',2,6) = hupu --> it wants total 6 letters but string is not long enough.
-
If the string is 'Chig' and query = SUBSTR('Chig',5,7) = As it wants to start from 5 letter but as total number of letter is 4 so it'll show NULL.
-
Length Function
Syntax- LENGTH(string)
Example- LENGTH('Sql Course')
Result- 10
SELECT first_name, SUBSTR(first_name,3,6), SUBSTR(first_name,3), last_name, LENGTH(last_name) FROM employees;
- Concat Function
Syntax- CONCAT(string1, string2)
Example- CONCAT('Sql', 'Course')
Result- SqlCourse
SELECT first_name, SUBSTR(first_name,3,6), SUBSTR(first_name,3),
last_name, LENGTH(last_name)
FROM employees;
SELECT CONCAT(first_name,last_name)
FROM employees;
SELECT CONCAT(CONCAT(first_name,last_name),employee_id)
FROM employees;
SELECT first_name || last_name || employee_id
FROM employees;
- Concat Function
Syntax- INSTR(string, substring[,position, occurrence])
Example- CONCAT('Sql Course', 'o')
Result- 6
SELECT INSTR('I am learning how to use functions in Oracle', 'o') FROM dual; -->Find first 'o'=16
SELECT INSTR('I am learning how to use functions in Oracle', 'o', 17) FROM dual; -->find first 'o' & checks from 17th letter = 20
SELECT INSTR('I am learning how to use functions in Oracle', 'o', 17, 3) FROM dual; --> finds the 3rd 'o' and will count from 17th letter = no 3rd 'o' after 17th.
SELECT INSTR('I am learning how to use functions in Oracle', 'o', 1, 3) FROM dual; --> find 3rd 'o' and start search from first = 32
SELECT INSTR('I am learning how to use functions in Oracle', 'o', -1, 3) FROM dual; --> =16
--> -1 means starts finding from backward. So if we count 'o' from backward 'TO'. This 'o' is 3rd from backward.
But we'll count that from first like I=1, space=1, a=3, m=4 and etc. So that 'TO' 'o' is 16.
SELECT INSTR('I am learning how to use functions in Oracle', 'o', -1, 1) FROM dual;
SELECT INSTR('I am learning how to use functions in Oracle', 'in', -1, 1) FROM dual;
SELECT INSTR('I am learning how to use functions in Oracle', 'in', 1, 1) FROM dual;
SELECT first_name,INSTR(first_name,'a') from employees;
- TRIM Function
Syntax- TRIM([[Leading|Trailing|BOTH] trim_charactor FROM] string)
Example- Trim(' Sql Course ')
Result- Sql Course
-
The trim function removes all the spaces before and after the string, which means if the trim function encounters any character, that's not a space character from both sides, then it stops removing the spaces. So the spaces in the middle of the stacks were not removed. It only removed the spaces before and after the string.
-
If you specify a trim character and the keyword from it will delete all the occurrences of the specified character from both sides.
-
If you type in the keyword leading before the trim character, which is the character we want to remove, it will remove that character from the beginning.
-
But if you write the keyword trailing, unlike leading the trim function will remove that character from the end of that string.
-
You can write both as well. It will have the same result with not writing anything.
SELECT TRIM (' My Name is Adam ') trm from dual;
SELECT TRIM (' ' FROM ' My Name is Adam ') trm from dual;
--> FROM keyword- We quoted space before FROM so means remove all space from both sides.
SELECT TRIM (BOTH ' ' FROM ' My Name is Adam ') trm from dual;
--> So writing both and not writing anything are the same.
Writing both is just for semantic clarity or increasing your code readability so you don't have to write it.
SELECT TRIM (LEADING ' ' FROM ' My Name is Adam ') trm from dual;
--> So what if I want to remove the spaces only from the beginning?
In this case, I delete the keyword **BOTH** from here & write **LEADING**.
SELECT TRIM (TRAILING ' ' FROM ' My Name is Adam ') trm from dual;
-->So what if I want to remove only the spaces after the text?
In this case I delete the **LEADING** keyword and write **TRAILING** instead.
SELECT TRIM (TRAILING 'm' FROM ' my Name is Adam ') trm from dual;
-->This 'm' means it wants to remove m from last but as last letter is space so it'll consider space so won't remove 'm'
Do not forget that the space character is just like any other character in Oracle.
SELECT TRIM (TRAILING 'm' FROM 'my Name is Adam') trm from dual;
--> Now there is no space at last so it'll consider 'm' as last and will remove 'm'. - Output - my Name is Ada
SELECT TRIM (TRAILING 'm' FROM 'my Name is Adammmmm') trm from dual;
--> It'll remove all 'm' from last as it work for space like removing all space - Output - my Name is Ada
SELECT TRIM (LEADING 'm' FROM 'my Name is Adam') trm from dual;
--> It'll remove from first
SELECT TRIM (BOTH 'm' FROM 'my Name is Adam') trm from dual;
--> It'll remove from both sides.
SELECT TRIM ('m' FROM 'my Name is Adam') trm from dual;
--> It'll remove from both sides. BOTH keyword doesn't matter. It's just for the readability.
SELECT TRIM ('m' FROM 'my Name is Ada') trm from dual;
SELECT TRIM (TRAILING 'm' FROM 'my Name is Ada') trm from dual;
SELECT TRIM (TRAILING 'my' FROM 'my Name is Ada') trm from dual;
--> I'll show error as more than 1 character is not allowed.
- LTRIM Function
Syntax- LTRIM(string, [trim_string])
Example- LTrim(' Sql Course ')
Result- Sql Course
- RTRIM Function
Syntax- RTRIM(string, [trim_string])
Example- RTrim(' Sql Course ')
Result- Sql Course --> ending space removed so starting space is there.
- But notice that this time we can write a trim string here. It does not have to be a single character as in the trim function. So this is quite important. That means you can write multiple characters here to remove them. And if you don't specify the trim string here, it removes the space characters from the beginning by default.
SELECT RTRIM (' my Name is Adam ') trm from dual;
SELECT LTRIM (' my Name is Adam ') trm from dual;
SELECT LTRIM (' my Name is Adam ', 'my') trm from dual;
SELECT LTRIM ('my Name is Adam', 'my') trm from dual; --> Remove 'my'
SELECT RTRIM ('my Name is Adam', 'my') trm from dual; --> Remove 'm' from last
SELECT RTRIM ('my Name is Adammmm', 'my') trm from dual; --> Remove all 'm' from last
SELECT LTRIM ('www.mywebsite.com', 'w.') trm from dual; --> Remove www.
SELECT LTRIM ('234234217www.mywebsite.com', '0123456789') trm from dual; --> remove all number from starting
SELECT rtrim(ltrim('www.website.com','w'), '.com') Trimmed Text from dual. --> remove www. & .com --> This is called a Nested Function.
- REPLACE Function
Syntax- REPLACE(string, string_to_replace[,replacement_string])
Example- REPLACE('Sql Course','s','*')
Result- Sql Cour*e
select first_name, replace(first_name,'a') rpl from employees; --> Removes all 'a'.
select first_name, replace(first_name,'a','-') rpl from employees; --> 'a' will replace by '-'
select first_name, replace(first_name,'le','-') rpl from employees;
--> it'll find 'exact 'le' and replace. It won't work like TRIM - like if finds 'l' or 'e'. Wants exact match.
select first_name, replace(first_name,'und','-') rpl from employees;
- LPAD Function
Syntax- LPAD(string,target_length,padding expression) --> Left Pad
Example- LPAD('Sql',10,'-')
Result- -------Sql
- RPAD Function
Syntax- RPAD(string,target_length,padding expression) --> Right Pad
Example- RPAD('Sql',10,'-')
Result- Sql-------
select first_name, LPAD(first_name,10,'*') pad from employees;
--> We want total 10 character string. It'll check the no. of letter in first name and if it is less then 10.
--> EG- Dhrupal - 7 letters and we want 10. It is LPAD so it;ll add '*' in remaining one-
--> Output = ---Dhrupal
select first_name, RPAD(first_name,10,'*') pad from employees;
select first_name, RPAD(first_name,6,'*') pad from employees;
--> where there is less than 6, it'll add '*' but where there is more than 6 , it'll show only 6.
--> EG- Dhrupal Output= Dhrupa
select first_name, LPAD(first_name,6,'*') pad from employees;
select first_name, LPAD('My name is ',20,'-') pad from employees;
select first_name, LPAD('My name is '||last_name ,20,'-') pad from employees;
SELECT round(12.136) FROM dual; --> Output - 12. coz second parameter is not specified.
You see that the result is 12 because as I've mentioned, if you don't specify the second parameter,
in other words, a precision number, it rounds up to the whole number by checking only the first precision.
So since it is less than five after decimal, it rounds down to 12.
SELECT round(12.536) FROM dual; --> Output - 13
SELECT round(12.536,2) FROM dual;--> Output - 12.54
SELECT TRUNC(12.536,2) FROM dual;--> Output - 12.53 --> the trunk simply deletes the rest of the given precision.
SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual; -->Output - 50,45.93
SELECT ROUND(55.953, -1), TRUNC(55.953, -1) FROM dual; -->Output - 60,50
SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual; -->Output - 50,45.93
SELECT ROUND(44.933, -1), TRUNC(44.936, -1) FROM dual; --> Output - 40,40
SELECT ceil(12.536) FROM dual; -->it rounds up to the nearest greatest whole number. 12.536 = 13, -12.536 = 11
SELECT ceil(12.001) FROM dual;
--> Output - 13. If it is more than 12 then it'll consider next number. 12.01,12.001,12.056 - all are 13.
SELECT ceil(12.999) FROM dual; --> Output - 13
SELECT ceil(12) FROM dual; --> Output - 12
SELECT floor(12.12) FROM dual; --> the closest lower integer number than the actual number. Output - 12
SELECT floor(12.99) FROM dual; --> Output - 12
MOD(m,n); m=Dividend, n=Divisor --> Reminder
SELECT MOD(8, 5) FROM dual; --> Output = 3. 5*1=5, 8-5=3
SELECT MOD(8, 2) FROM dual; --> Output = 0. 2*4=8, 8-2=0
SELECT MOD(1800, 70) FROM dual; --> Output = 50. 70*20=140, 180-140=40, 70*5=350, 400-350=50
- So here nesting means you can use the output of a function as the input for another function.
- And the nested functions are evaluated starting from the innermost function and executed one by one toward to the outermost one.
- I mean, the innermost function is executed first. The result of the innermost function becomes the input for the outer function. And this goes on like that until it reaches to the outermost function.
SELECT SUBSTR('John Smith', INSTR('John Smith', ' ')+1,LENGTH('John Smith')) output
FROM dual; --> Output - Smith. When we want to only fetch surname
SELECT SUBSTR('John Smith', INSTR('John Smith', ' ')+1) output
FROM dual; --> Output - Smith. Same result as above
SELECT first_name|| ' ' || last_name full_name,
SUBSTR(first_name|| ' ' || last_name,
INSTR(first_name|| ' ' || last_name, ' ')+1) output
FROM employees;
--> Output - Last name.
INSTR(first_name|| ' ' || last_name,** ' '**)+1)- this bolded space is for after when to start so after that space last name is starting.
SELECT first_name|| ' ' || last_name full_name,
SUBSTR(concat(concat(first_name,' '),last_name),
INSTR(first_name|| ' ' || last_name, ' ')+1) output
FROM employees;
- In fact, the Oracle database doesn't store a date. Instead, it records a date time.
- To check the date format, in SQL Developer - tools - Preferences - Database - NLS. You can change the format from it as you want.
- DD-MM-RR fmHH12:fmMI:SSXFF AM TZR = DD=Day, MM=Mon, RR=year, HH=Hour, 12/24=Hours Format, MI=Min, SS=Sec, X= Seperator - It shows how you separate the fractional seconds. It can be a dot sign or a comma, etcetera. It's local to your country. F=F is the fractional seconds. It shows more detailed second values as a fraction like a decimal number. You know, if you do not write h h 24 you need to write am or PM here to show that it is morning or afternoon. TZR=Time Zone region. These elements are called format models and you will see all these and more format models in detail.
- DATE - It is the most commonly used and the standard date type in Oracle. It's those year, month, day as well as hour, minute and second. Yes. Even though it's called date. Bear in mind that it stores the time components. I mean, hours, minutes and seconds as well. This is a good thing to remember.
- TIMESTAMP - The timestamp data type is an extension of the date data type. This data type also stores year, month, day, hour, minute and second. But in addition to all these, it also stores the fractional seconds, which are not stored by the date data type.
- TIMESTAMP WITH TIME ZONE - As the name implies, this data type stores both timestamp as well as time zone data.The main feature of this data type indicates the time zone values like plus to minus eight, etcetera.
- TIMESTAMP WITH LOCAL TIME ZONE - This data type is similar to timestamp with time zone, but the previous time zone shows the database time zone unlike this one. This data type doesn't show the time zone of where your database is. When a time zone is stored in the database, they both save the database time zone. So this data is stored by being normalized to the database time zone. And it converts this value into the local time zone of the location where the user performs a query. I mean, not the time zone of where the database is.
-
SYSDATE - If you are in one country and operating system, which the database is installed is in a different country. When you call the state function, it returns the operating system state and time, not the time and date where you are. Actually, I'd say that this is the most commonly used date function. However, the rest is also very important, depending on your needs.
-
CURRENT_DATE - For example, assume that you are in the UK but your database is located in the US. When you call the sea state function, it will return the date and time of the United States since your system resides in the US. But if you call the current date function, unlike the date because you are logged in from the UK, it will return the current date and time of the United Kingdom this time. Please note that this can also be a tricky question in the certification exams or job interviews.
-
SESSIONTIMEZONE - This might be helpful to know, especially if you perform lots of operations with dates and work on databases in different time zones. So if you call the session time zone function, it will display which time zone your session is in.
-
SYSTIMESTAMP - It also shows the fractional seconds and the timezone as well. I mean, it actually returns a timestamp with time zone value that represents the system date and time, including fractional seconds and time zone. The important thing to remember is that it returns the date and time of the system where your database is installed, not your local time. So if you are accessing a database in a different time zone, it will return the time and time zone of the place where the database is stored, not your local time zone. For example, if your database is in London but you are in New York, the time stamp will return a time that is five hours ahead of your time. It basically takes into account the different time zones.
-
CURRENT_TIMESTAMP - It returns the current date and time of the user's session or where the user has logged in from. It is similar to the current date function that it returns the date and time of your session instead of the database. It is also similar to the C's time stamp function because it returns the fractional seconds. The returning data type of this function is a timestamp with time zone, which includes dates, time, fractional seconds, or in other words, milliseconds and a time zone.
-
Here's an important tip that you should know. If there is a word current in the name of a function, that means my session or your session or a user's session. But if the function name includes the word SES, which stands for system, as you know, it means the date and time of the system where the database is installed. Now let's see how we can use these functions with an SQL query. As you can see, we use them just like we use a column. If you noticed, these functions do not take in an arguments. We just call them by their names and they return the related values to us.
SELECT sysdate FROM dual;
SELECT sysdate, current_date, sessiontimezone, systimestamp, current_timestamp
FROM dual;
SELECT sysdate FROM dual;
SELECT sysdate, sysdate + 4 FROM dual;
SELECT sysdate, sysdate - 4 FROM dual;
SELECT sysdate, sysdate + 1/24 FROM dual; --> to work with hours
Go to tools change format to HH24:MI:SS
SELECT sysdate, sysdate + 1/(24*60) FROM dual; --> to work with minutes
SELECT employee_id, hire_date,sysdate FROM employees;
SELECT employee_id, hire_date,sysdate, sysdate-hire_date worked_in_days
FROM employees;
SELECT employee_id, hire_date,sysdate,trunc(sysdate-hire_date) worked_in_days
FROM employees;
SELECT employee_id, hire_date,sysdate,trunc((sysdate-hire_date)/365) worked_in_years
FROM employees
ORDER BY worked_in_years DESC;
SELECT sysdate, add_months(sysdate,2) FROM dual;
SELECT sysdate, add_months(sysdate,-2) FROM dual;
SELECT sysdate, add_months(sysdate,30) FROM dual;
SELECT sysdate, add_months('12-JUL-21',30) FROM dual;--> It'll also work if I write ('12JUL21',30)
SELECT employee_id, hire_date,
trunc(hire_date,'MONTH') truncated_result, round(hire_date,'MONTH') rounded_result
FROM employees;
SELECT employee_id, hire_date,
trunc(hire_date,'YEAR') truncated_result, round(hire_date,'YEAR') rounded_result
FROM employees;
SELECT extract(year from sysdate) extracted_result FROM dual;
SELECT extract(month from sysdate) extracted_result FROM dual;
SELECT extract(day from sysdate) extracted_result FROM dual;
SELECT next_day(sysdate,'SUNDAY') next_day_result FROM dual;
SELECT last_day(sysdate) last_day_result FROM dual;
SELECT last_day('04-JUL-20') last_day_result FROM dual;
--Implicit Conversion FROM a VARCHAR2 value TO a "NUMBER" value.
SELECT * FROM EMPLOYEES WHERE salary > '5000';
--Implicit Conversion FROM a VARCHAR2 value TO a "DATE" value.
SELECT * FROM EMPLOYEES WHERE HIRE_DATE = '17-JUN-03';
--Implicit Conversion FROM a NUMBER value TO a VARCHAR2 value.
SELECT DEPARTMENT_ID || DEPARTMENT_NAME FROM DEPARTMENTS;
--Implicit Conversion FROM a DATE value TO a VARCHAR2 value.
SELECT FIRST_NAME || SYSDATE FROM EMPLOYEES;
- This conversion is done by typing some code, not automatically. So this is why we call them explicit conversions. It means they need to be written by us.
- nls_parameter is optional.
SELECT first_name, hire_date FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'YYYY') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'YY') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'RR') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'YEAR') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'MM') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'MM-YYYY') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'MON-YYYY') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'MON-yyyy') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'mon-yyyy') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'Mon-yyyy') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'MONTH-yyyy') "Formatted Date" FROM employees; --> Output - JULY 2023
SELECT first_name, hire_date, to_char(hire_date,'Month-yyyy') "Formatted Date" FROM employees; --> Output - July 2023
SELECT first_name, hire_date, to_char(hire_date,'DD-Month-yyyy') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'DY-Month-yyyy') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'Dy-Month-yyyy') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'Day-Month-yyyy') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'Dy-Month-yyyy HH12') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'Dy-Month-yyyy HH24') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'Dy-Month-yyyy HH24:MI') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'Dy-Month-yyyy HH24:MI:SS') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(sysdate,'Dy-Month-yyyy HH24:MI:SS') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'DDTH-Month-yyyy HH24:MI:SS') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'DDTH-MMTH-yyyy HH24:MI:SS') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'DDSP-MMTH-yyyy HH24:MI:SS') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'DDSPTH-MMTH-yyyy HH24:MI:SS') "Formatted Date" FROM employees;
SELECT first_name, hire_date, to_char(hire_date,'DDTHSP-MMTH-yyyy HH24:MI:SS') "Formatted Date" FROM employees;``
SELECT salary*commission_pct*100 "Original",
TO_CHAR(salary*commission_pct*100, '$999,999.00') "Formatted Version"
FROM employees WHERE commission_pct IS NOT NULL;
SELECT salary*commission_pct*100 "Original",
TO_CHAR(salary*commission_pct*100, 'L999,999.00') "Formatted Version"
FROM employees WHERE commission_pct IS NOT NULL;--> Output - L shows the local currency
To change the local territory - tools - preferences-database-nls-territory
SELECT salary*commission_pct*100 "Original",
TO_CHAR(salary*commission_pct*100, '$099,999.00') "Formatted Version"
FROM employees WHERE commission_pct IS NOT NULL;
SELECT salary*commission_pct*100 "Original",
TO_CHAR(salary*commission_pct*100, '$009,999.00') "Formatted Version"
FROM employees WHERE commission_pct IS NOT NULL;
SELECT to_number('$5,322.33', '$99,999.00') formatted_number FROM dual;--> $5,322.33 - $99,999.00--> Format should be same
not like- 5,322 - $9,999.00-->Dollar should be there
not like- $5,322 - $999.00 ---> Atleast 4 numbers as in 5,322.
- As you know from the previous lessons, performing some operations with the null values may result in getting null values or nothing as the result. This looks weird, especially in some reports or applications. So to avoid this situation, you need to handle the null values. That's where the Nvl function comes into play.
SELECT employee_id, salary, commission_pct, salary + salary * commission_pct
FROM employees;
SELECT employee_id, salary, commission_pct, salary + salary * nvl(commission_pct,0) nvl_new_sal
FROM employees;--> NULL will replace by 0.
salary commsn output
2500 0.2 2500*0.2=500+2500=3000
5500 NULL 5500
SELECT first_name, last_name,
length(first_name) len1, length(first_name) len2,
nullif(length(first_name),length(last_name)) result
FROM employees;
SELECT coalesce(null,null,null,1,2,3, null) FROM dual;
SELECT coalesce(null,null,null,null) FROM dual;
SELECT state_province, city, coalesce(state_province,city) FROM locations;
city & state has almost same name but state has multiple NULL value
so by doing this where state is null, it;ll take city value.
- The CASE expression facilitates if-then-else conditional logic. There are two variants of the CASE expression. The simple CASE expression lists the conditional search item once, and equality to the search item is tested by each comparison expression. The searched CASE expression lists a separate condition for each comparison expression. So the simple case expression takes at least 3 mandatory parameters but it can take more also. CASE expression WHEN comparison_expression THEN return_expression END;
--Example 1: (Buna bir bak istersen böyle yaptım ama fontu büyüttük ya senin SQL developerda sığarmı böyle)
SELECT first_name, last_name, job_id, salary, CASE job_id
WHEN 'ST_CLERK' THEN salary * 1.2
WHEN 'SA_REP' THEN salary * 1.3
WHEN 'IT_PROG' THEN salary * 1.4
ELSE 0
END "UPDATED SALARY"
FROM employees;
--Example 2:
SELECT first_name, last_name, job_id, salary,
CASE job_id
WHEN 'ST_CLERK' THEN salary * 1.2
WHEN 'SA_REP' THEN salary * 1.3
WHEN 'IT_PROG' THEN salary * 1.4
ELSE salary
END "UPDATED SALARY"
FROM employees;
--Example 3:
SELECT first_name, last_name, job_id, salary,
CASE
WHEN job_id = 'ST_CLERK' THEN salary*1.2
WHEN job_id = 'SA_REP' THEN salary*1.3
WHEN job_id = 'IT_PROG' THEN salary*1.4
ELSE salary
END "UPDATED SALARY"
FROM employees;
--Example 4:
SELECT first_name, last_name, job_id, salary,
CASE WHEN job_id = 'ST_CLERK' THEN salary*1.2
WHEN job_id = 'SA_REP' THEN salary*1.3
WHEN job_id = 'IT_PROG' THEN salary*1.4
WHEN last_name = 'King' THEN 2*salary
ELSE salary END "UPDATED SALARY"
FROM employees;
--Example 5:
SELECT first_name, last_name, job_id, salary,
CASE
WHEN job_id = 'AD_PRES' THEN salary*1.2 --> if a particular rows applies multiple condition then I'll run the very first condition
WHEN job_id = 'SA_REP' THEN salary*1.3
WHEN job_id = 'IT_PROG' THEN salary*1.4
WHEN last_name = 'King' THEN 2*salary --> multiple column can only done in search case expression
ELSE salary
END "UPDATED SALARY"
FROM employees;
--Example 6:
SELECT first_name, last_name, job_id, salary
FROM employees
WHERE (CASE
WHEN job_id = 'IT_PROG' AND salary > 5000 THEN 1
WHEN job_id = 'SA_MAN' AND salary > 10000 THEN 1
ELSE 0
END) = 1;
SELECT DECODE (1, 1,'One', 2,'Two') result FROM dual;
SELECT DECODE (25, 1,'One', 2,'Two',3,'Three','Not Found') result FROM dual;
SELECT first_name, last_name, job_id, salary,
DECODE(job_id,'ST_CLERK',salary*1.20,
'SA_REP' ,salary*1.30,
'IT_PROG' ,salary*1.50 ) as updated_salary
FROM EMPLOYEES;
SELECT first_name, last_name, job_id, salary,
DECODE(job_id,'ST_CLERK', salary*1.20,
'SA_REP' , salary*1.30,
'IT_PROG' , salary*1.50,
salary) as updated_salary
FROM EMPLOYEES;
- So why do we need to use the group functions? Let me explain this with some real world scenarios. Assume that you're working in a company. Your managers may want to know how many employees the company has, or they may request from you to find the employees who earn the maximum salary among the IT programmers, for example. Or as an employer, they may wonder what the average salary your company pays to its employees. Or they may want to know how many employees earn more than 10,000 salary. There might be so many questions like this that you need to answer quickly. We frequently need this kind of information or some reports that include this type of info. In business life. So how can we find this information in the database? As you might guess, this kind of information can easily be acquired using the group functions. So how do we use the group functions?
