In this post, we will understand the difference between T-SQL and PL-SQL.T-SQLIt is a Microsoft product.It is known as Transact Structure Query language.It gives a high degree of control to the developers/programmers.It works its best, and provides good performance with Microsoft SQL server.It is easy.It is simple to understand.It allows the insertion of multiple rows into a table.This is done with the help of the ‘BULK INSERT’ statement.The ‘SELECT INTO’ statement is used in T-SQLIn this, the ‘NOT EXISTS’ clause can be used with the ‘SELECT’ statements.PL-SQLIt is an Oracle product.It is known as Procedural Language Structural Query Language.It is ... Read More

In this post, we will understand the difference between clustered index and non-clustered index.Clustered indexIt is quick.It requires less memory to perform operations.The index is the main data.A table can have one clustered index only.It has inherent ability to store data on the disk.It can store pointers to block not to data.The leaf nodes contain actual data.The clustered key defines the order of data within table.It is a type of index where the table records are physically reordered to match with the index.Non-clustered indexIt is slower.It requires more memory to perform operations.The index is a copy of data.A table can ... Read More

In this post, we will understand the difference between grant and revoke.GrantIt is a DCL command.It grants permissions to users on database objects.It can also be used to assign access rights to users.For every user, the permissions need to be specified.When the access is decentralized, permission granting is easier.Syntax:grant privilege_name on object_name to {user_name | public | role_name}RevokeIt is a DCL command.It removes permissions if they are granted to users on database objects.It takes away/revokes the rights of the users.If access for a user is removed, all specific permissions provided by that user to others will be removed.If decentralized access ... Read More

Although quite infrequent, you may come across situations wherein you need to define the primary key on an existing table. This can be achieved using the ALTER TABLE statement.The syntax is −ALTER TABLE table_name ADD PRIMARY KEY (column_name1, column_name2, …., columns_nameN)As can be seen from the above syntax, you can define PRIMARY KEY on multiple columns. When you have defined the PRIMARY KEY on multiple columns, the condition is that the column pairs should have unique and non-null values. Thus, if the PRIMARY KEY is defined on (column1, column2), the values (value1, value2), (value3, value2), and (value1, value4) are allowed. ... Read More

Let us create a new table containing a single timestamp column −CREATE TABLE timestamp_test(    ts timestamp );Now let us populate it with some data −INSERT INTO timestamp_test(ts) VALUES(current_timestamp), (current_timestamp+interval '5 days'), (current_timestamp-interval '18 hours'), (current_timestamp+interval '1 year'), (current_timestamp+interval '3 minutes'), (current_timestamp-interval '6 years');If you query the table (SELECT * from timestamp_test), you will see the following output −ts2021-01-30 19:23:24.0080872021-02-04 19:23:24.0080872021-01-30 01:23:24.0080872022-01-30 19:23:24.0080872021-01-30 19:26:24.0080872015-01-30 19:23:24.008087Now, in order to extract hour, minute, etc. from the timestamp column, we use the EXTRACT function. Some examples are shown below −SELECT EXTRACT(HOUR from ts) as hour from timestamp_testOutput −hour19191191919Similarly −SELECT EXTRACT(MONTH from ts) as ... Read More

Often, we have some very long table names, and writing the table name every time is troublesome. We can use aliasing to help us there, thanks to which, we will need to write the long table name only once.The table aliases are generally written in the FROM part of the statement, or the JOIN part.For example, consider that we have two tables, marks, and student_info, defined respectively below −marksnameroll_noperc_marksAniket1224Siddhi4565Yash2642Isha5687student_infonameroll_noagegenderAniket1226MIsha5625FSiddhi4523FYash2625MNow, if you want to see the name, roll_no, perc_marks, and age of the student in one query, your query will look like this −SELECT marks.name, marks.roll_no, marks.perc_marks, student_info.age FROM marks LEFT ... Read More

Suppose you have a table user_info that contains the state and district of different users. An example is given below −namedistrictstateAnilMumbaiMaharashtraJoyJhalawarRajasthanRonPuneMaharashtraReenaMeerutUttar PradeshNow, if you want to combine the state and district in a single field called location, this is how you should be able to do it −SELECT name, district || ', ' || state as location from user_infoThe || operator is the string concatenation operator. The output will be −namelocationAnilMumbai, MaharashtraJoyJhalawar, RajasthanRonPune, MaharashtraReenaMeerut, Uttar PradeshSimilar operations can also be performed on numerical values. Suppose you have a table marks containing the total marks scored by students and the maximum ... Read More

Suppose you have a table user_info containing the names of users and their addresses. An example is given below −nameaddressAnilAndheri, Mumbai, MaharashtraJoyChandni Chowk, DelhiRonBandra, Mumbai, MaharashtraReenaOld Airport Road, Bengaluru, KarnatakaNow, if you want to just extract the information of users who stay in Mumbai, you can do that using the LIKE command and the % operator.SELECT * from user_info where address LIKE '%Mumbai%'The output will benameaddressAnilAndheri, Mumbai, MaharashtraRonBandra, Mumbai, MaharashtraNotice that we have added % operator on both sides of Mumbai. This means that anything can precede Mumbai and anything can be after Mumbai. We just want the string to ... Read More

Suppose you have a table exam_scores containing 5 columns. An example is given below with some dummy data.nameroll_nosubjecttotal_marksmarks_obtainedAnil1English10056Anil1Math10065Anil1Science10045Roy2English10078Roy2Math10098Roy2Science10067Now, one student could have sat for exams of multiple subjects, and therefore, there are multiple rows for 1 student. If you wish to find out the total number of students in the class, you may want to find the number of distinct values of roll_no. You can apply the distinct constraint on a specific column as follows −SELECT DISTINCT ON (roll_no) name, roll_no FROM exam_scores ORDER BY roll_no DESCHere’s what the output of the above query will look like −nameroll_noRoy2Anil1You can also ... Read More

If you are a programmer, you may be very familiar with IF-ELSE statements. The equivalent in PostgreSQL is CASE WHEN.Let’s understand with an example. If you have table marks containing percentage marks of a student, and you want to find out whether the students have passed or failed. An example table is given below.nameperc_marksAnil24Joy65Ron42Reena87Say the passing marks are 40. Now, if the student has scored above 40 marks, we want to print ‘PASS’ against that student’s name, otherwise ‘FAIL’. This is how you can do it −SELECT name, CASE WHEN perc_marks >= 40 THEN 'PASS' ELSE 'FAIL' END status from ... Read More