Trending February 2024 # 6 Commonly Used Different Kinds Of Sql Constraints # Suggested March 2024 # Top 10 Popular

You are reading the article 6 Commonly Used Different Kinds Of Sql Constraints updated in February 2024 on the website Achiashop.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 6 Commonly Used Different Kinds Of Sql Constraints

Introduction to SQL Constraints

Web development, programming languages, Software testing & others

In SQL, we have many different kinds of constraints. Let us look at the following few constraints in this article.

NOT NULL

CHECK

UNIQUE

PRIMARY KEY

FOREIGN KEY

DEFAULT

Different Kinds of SQL Constraints

Below given are different kinds:

1. NOT NULL Constraint

This constraint is used when you do not want any value in that particular column to be a Null value. This means that we cannot insert a Null value for that column while inserting a new row in the table. Therefore, every field in this column always has a non-Null value. A null value means that a particular field has been left empty, and values such as zero or blank space do not come under Null values.

Let us look at an example to create a table called Employee, having 5 columns, where empid, name, and mobile columns do not accept NULL values.

CREATE TABLE Employee ( empid INT NOT NULL, name VARCHAR(20) NOT NULL, dob DATE, mobile VARCHAR(10) NOT NULL, address VARCHAR(20) ); 2. CHECK Constraint

This constraint limits the values that can be entered in that particular column of the table. To understand this better, let us take the example of passing marks in an exam. The range of values for these marks can only be from 35 to 100. To ensure that only values in this range are entered, we can create a CHECK constraint.

CREATE TABLE Employee ( empid INT NOT NULL, name VARCHAR(20) NOT NULL, dob DATE, mobile VARCHAR(10) NOT NULL, address VARCHAR(20) ); 3. UNIQUE Constraint

This constraint is applied to ensure that the particular column accepts only unique values, and repetitive values are not allowed with such a constraint on the column. We can create multiple UNIQUE constraints on various columns in a table. A UNIQUE constraint allows NULL values to be entered.

Let us look at an example of enforcing the UNIQUE constraint. In this example, we are creating a column called mobile in the table Employee, which is to be unique and cannot accept the same mobile number twice.

CREATE TABLE Employee ( empid INT NOT NULL, name VARCHAR(20) NOT NULL, dob DATE, mobile VARCHAR(10) NOT NULL UNIQUE, address VARCHAR(20) ); 4. PRIMARY KEY Constraint

This constraint is used to identify a particular column or a group of columns that can uniquely identify a row in the table. With the PRIMARY KEY constraint in place, any row cannot have a duplicate value. We cannot have NULL as the value for such a column. Even though both a PRIMARY KEY constraint and a UNIQUE constraint impose that the values are unique, we use a UNIQUE constraint when we do not want to declare the column as Primary Key but still want the values in that column to be unique. We can have only a single PRIMARY KEY column or a group of columns in a table, but we can declare many individual columns UNIQUE.

CREATE TABLE Employee ( empid INT NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, dob DATE, mobile VARCHAR(10) NOT NULL UNIQUE, address VARCHAR(20) ); 5. FOREIGN KEY Constraint

This constraint helps the data in one table to establish a relationship with the data in another table in the database. Foreign Key can be a single column or a set of columns. For example, let us consider two tables, Employee and Departments. Suppose we a column called depicted in Employee and a departed in Departments. Then, we can reference the departed of Employee to the departed of Departments if the columns match. In this case, the column in Employee becomes a foreign key reference to the column in the Department table, which is a primary key.

CREATE TABLE Employee ( empid INT NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, dob DATE, mobile VARCHAR(10) NOT NULL UNIQUE, address VARCHAR(20), depicted INT FOREIGN KEY REFERENCES Department(depicted) ); 6. DEFAULT Constraint

This constraint is used to specify the default value for a particular column in the table. This way, if there is no value inserted for that column explicitly, the database engine can always refer to the default value specified and insert that in the column. If we have inserted a column with the constraint NOT NULL and the constraint DEFAULT, we do not need to define a default value explicitly. Even without giving a default value, the particular row will be inserted in the table.

Let us look at an example where we have entered the place in the address as default to have the value ‘India’.

CREATE TABLE Employee ( empid INT NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, dob DATE, mobile VARCHAR(10) NOT NULL UNIQUE, address VARCHAR(20) DEFAULT ‘India’, depicted INT FOREIGN KEY REFERENCES Department(depicted), ); Conclusion

SQL constraints help the developer by specifying restrictions and rules for the data that is to be inserted in the table. Constraints can be applied at the column level, just to the particular column or at the table level, where the constraints are applied to the complete table. These constraints restrict the kind of information that can be inserted into the table. This guarantees the correctness and consistency of the data in the table. In case of any violations of the rules specified by the constraints, the action is terminated.

Recommended Articles

We hope that this EDUCBA information on “SQL Constraints” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

You're reading 6 Commonly Used Different Kinds Of Sql Constraints

List Of Operators Used For Regexp In Sql With Syntax

Introduction to SQL REGEXP

A regular expression in standard query language (SQL) is a special rule that is used to define or describe a search pattern or characters that a particular expression can hold. For example, a phone number can only have 10 digits, so in order to check if a string of numbers is a phone number or not, we can create a regular expression for it. It is an in-built specification supported in almost all SQL databases.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Regular expressions are very helpful as they let us place multiple lines of code or information in just 1 line. It is particularly helpful in SQL databases when we want to perform validation tasks like if the information provided is a valid PIN code, Contact No, email address, etc. Regular expressions also help in pattern matching or searching the database.

List of Operators Used for REGEXP in SQL

Here is the list of some of the most frequently used operators or metacharacters for making regular expressions in SQL.

Operator

Operator Name

(.) Any character – Dot Quantifier Matches any single character in the character set of the database.

(*) Asterisk or Star Quantifier Matches zero or more occurrences of the subexpression/ strings preceding to it.

(+) Plus Quantifier Matches one or more occurrences of the subexpression/ strings preceding to it.

(?) Question mark Quantifier Matches zero or one occurrence of the subexpression/ strings preceding to it.

[ABC] / [abc] Matching Character List Matches any character mentioned in the list.

[^ABC] / [^abc] Non-Matching Character List Matches any character except the ones mentioned in the list.

[0-9] Digit List Matches any digit from 0 to 9.

{a} Exact count Interval Matches exact ‘a’ occurrences of subexpression or string preceding to it.

{a,} At Least one count Interval Matches at least ‘a’ occurrences of subexpression or string preceding to it.

{a,b} Between count Interval Matches at least ‘a’ occurrences of subexpression or string preceding to it but not more than ‘b’ occurrences.

(^) Caret Quantifier Matches an expression only if it occurs at the beginning of a line.

($) Dollar or End Quantifier Matches an expression only if it occurs at the end of a line.

Vertical Bar Quantifier It is used to isolate different alternatives in a regular expression.

[[:class:]] Class Quantifier Matches the character class , for example , matches [[:digit:]] to digits, [[:space:]] to space, [[:alnum:]_] to alpha numerics, etc.

In the table above, we have tried to incorporate the most basic and frequently used meta-characters or quantifiers used for creating complex regular expressions. On the basis of this table, you can create other quantifiers such as +? Or *? for non-greedy versions of plus (+) quantifier and asterisk (+) quantifier, i.e matching just zero or one occurrences.

Examples of SQL REGEXP

Here are a few examples to illustrate the use and functions of different quantifiers in regular expressions.

In order to do so, let us first create a ‘customer_details’ table which contains customer id, his or her name, contact details, and the city where they live. We can use the following SQL statements to perform the task.

CREATE TABLE customer_details ( customer_id integer, customer_name character varying(255), city character varying(255), contact_no character varying(255), email_address character varying(255) );

Having created the customer_details table, let us now feed some information into the table columns using insert statements as shown below.

select * from customer_details

Output:

Example #1

SQL query to Illustrate the use of Dot (.) quantifier.

SELECT * FROM customer_details WHERE customer_name ~ 'K.';

Output:

Any Character Dot (.) quantifier matches with any string containing that character. In this example, customer_names with capital ‘K’ in them will be matched.

Example #2

SQL query to Illustrate use of Star (*) quantifier.

SELECT * FROM customer_details WHERE customer_name ~ 'Ked*';

Output:

Example #3

SQL query to Illustrate the use of Plus(+) quantifier.

SELECT * FROM customer_details WHERE email_address ~ 'gmail+';

Output:

Example #4

SQL query to Illustrate the use of Question Mark (?) quantifier.

SELECT * FROM customer_details WHERE city ~ 'Los?';

Output:

Example #5

SQL query to Illustrate the use of [… ] Character List quantifier.

SELECT * FROM customer_details WHERE customer_name ~ '[CA]';

Output:

[…] quantifier matches with strings that contain any character mentioned in the list. In this example, customer_names containing Capital A and C will be selected.

Example #6

[…] quantifier matches with strings that contain any character mentioned in the list. In this example, customer_names containing Capital A and C will be selected.

SQL query to Illustrate the use of [^…] non-matching character list quantifier.

SELECT * FROM customer_details WHERE customer_name ~ '^[^CA]';

Output:

[^…] quantifier matches a string that does not contain any of the characters mentioned in the list. In this case, customer_names which do not start with A or C will be selected.

Example #7

[^…] quantifier matches a string that does not contain any of the characters mentioned in the list. In this case, customer_names which do not start with A or C will be selected.

SQL query to Illustrate the use of Digit [0-9] quantifier.

SELECT * FROM customer_details WHERE contact_no ~ '[8-9]';

Output:

[0-9] quantifier matches for digits. In this example, [8-9] regexp matches with contact_nos which contain digits 8 or 9.

Example #8 SELECT * FROM customer_details WHERE contact_no ~ '^[8]';

Output:

SELECT * FROM customer_details WHERE city ~ '^New';

Output:

‘^’ quantifier matches an expression if and only if a string or line begins with it. For example, contact_no starting with 8 and cities starting with ‘New’.

Example #9 SELECT * FROM customer_details

Output:

The vertical bar is used to create one or more versions of the matching subexpression. For example, in this case, matching those contact numbers which starts with 8 or 9.

Example #10

SQL query to Illustrate the use of Dollar or End ($) quantifier.

SELECT * FROM customer_details WHERE contact_no ~ '8$';

Output:

$ matches an expression when it occurs at the end of a string. In this case, we are trying to find those contact numbers which end in digit 8.

Recommended Articles

We hope that this EDUCBA information on “SQL REGEXP” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

Learn The Examples Of Sql Datediff()

Introduction to SQL Datediff()

In SQL server suppose we have dates in our data and we want to know the difference between those dates then we can use the DATEDIFF function to know the difference between those dates in days, months, or years. So this function returns an integer as output and to understand more about this function lets know it’s syntax first.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax of Datediff() in SQL DATEDIFF (interval, startdate, enddate)

1. Interval – This is also called datepart and it is provided as a string to this function. This argument can be anything that represents a time interval like a month, week, day, year. We can also specify the quarter of the year.

year, yyyy, yy = Year SELECT DATEDIFF(year, '2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); quarter, qq, q = Quarter SELECT DATEDIFF(quarter,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); month, mm, m = month SELECT DATEDIFF(month, '2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); dayofyear = Day of the year SELECT DATEDIFF(dayofyear,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); day, dy, y = Da SELECT DATEDIFF(day,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); week, ww, wk = Week SELECT DATEDIFF(week,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); hour, hh = hour SELECT DATEDIFF(hour,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); minute, mi, n = Minute SELECT DATEDIFF(minute,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); second, ss, s = Second SELECT DATEDIFF(second,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); millisecond, ms = Millisecond SELECT DATEDIFF(millisecond,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000'); microsecond, mcs = Microsecond SELECT DATEDIFF(microsecond,'2010-12-31 23:59:59.9999999', '2011-01-01 00:00:00.0000000');

2. startdate, enddate – These are the actual dates to get the difference between. This is a mandatory parameter.

This function works in the SQL server starting from the 2008 version, Azure SQL Data Warehouse, Azure SQL Database, Parallel Data Warehouse.

Return Value

The return value is an int and is expressed by the datepart or the interval boundary which is the difference between the start and end date.

If the range of the return value for int is out of[-2,147,483,648 to +2,147,483,647], DATEDIFF function returns an error. The max difference between the start and end date is 24 days, 20 hours, 31 minutes, and 23.647 seconds for the millisecond. The max difference is 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds for the second.

If the start and end date have a date with different data type then DATEDIFF will set 0 the missing parts of the other date which has lower precision

The above queries have the same start and end values. These are adjacent dates and the difference between them is a hundred nanoseconds (.0000001 second). The start and end dates cross one calendar and the result of each query is 1.

Examples

Here are the examples mention below

Example #1 – Calculating Age select ID,emp_name,emp_dateOfBirth from Employee

We have the above table Employee which consists of the date of birth and from this, we will calculate the age in terms of a year, month, and days in 2 steps

Step 1: Creating a function

CREATE FUNCTION fnEmpComputeAge(@EmpDOB DATETIME) RETURNS NVARCHAR(50) AS BEGIN DECLARE @AgeTempdate DATETIME, @AgeYears INT, @AgeMonths INT, @AgeDays INT SELECT @AgeTempdate= @EmpDOB SELECT @AgeTempdate=DATEADD(YEAR, @AgeYears, @AgeTempdate) SELECT @AgeTempdate=DATEADD(MONTH, @AgeMonths, @AgeTempdate) SELECT @AgeDays=DATEDIFF(DAY, @AgeTempdate,GETDATE()) DECLARE @EmpAge NVARCHAR(50) SET @EmpAge=Cast(@AgeYears AS NVARCHAR(4))+' AgeYears '+Cast(@AgeMonths AS NVARCHAR(2))+' AgeMonths '+Cast(@AgeDays AS NVARCHAR(2))+' AgeDays Old' RETURN @EmpAge End

In the above example, we have created a SQL Function to calculate the age of the employee from the DOB so the function takes @EmpDOBas a parameter and returns NVARCHAR(50). We will see this in action when we run this function. In step, we have created this function.

Then we add the calculated years in the @AgeTempdate using the DATEADD function.

Similarly, we calculated the month and added in @AgeTempdate, and then it is used to calculate days. Next, we declared @EmpAge and set it to the concatenation of the final output. Since the calculation result is in int we used Cast function to convert it into nvarchar.

Step 2: Using the function in the query

select ID,emp_name,emp_dateOfBirth,dbo.fnEmpComputeAge(emp_dateOfBirth) as EmpAge from Employee

The result is as follows:

As we can see we have used dbo.fnEmpComputeAge function and passed emp_dateOfBirth to calculate EmpAge and the result is as above.

Example #2 – Using scalar functions and subqueries for start and end date SELECT DATEDIFF(day, (SELECT MIN([ShipDate])FROM Sales.SalesOrderHeader), (SELECT MAX([ShipDate])FROM Sales.SalesOrderHeader)) as ShippingDateDiff;

The result is as follows:

In this example, we have calculated the shipping date difference using scalar functions and scalar subqueries for min and max.

Example #3 – Using ranking functions for the start date argument SELECT FirstName as first_name,LastName as last_name, DATEDIFF(day,ROW_NUMBER() OVER (ORDER BY DepartmentName),SYSDATETIME()) AS row_number FROM dbo.DimEmployee;

The result is as follows:

In this function, we have used ROW_NUMBER() ranking function as the start date argument.

Example #4 – Using an aggregate window function for the start date argument SELECT FirstName as first_name,LastName as last_name,DepartmentName as department_name, DATEDIFF(year,MAX(HireDate) OVER (PARTITION BY DepartmentName),SYSDATETIME()) AS HireInterval FROM dbo.DimEmployee

Conclusion

Hopefully, now you know what DATEDIFF() is in the SQL server and how it is used to calculate results the difference between date according to datepart.

Recommended Articles

We hope that this EDUCBA information on “SQL DATEDIFF()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

Different Versions Of Imagemagick In Detail

Introduction to Imagemagick version

Web development, programming languages, Software testing & others

Versions of Imagemagick

Imagemagick was created in 1987 by John Cristy, where initially, it was used to convert 24-bit images to 8-bit images with fewer colors than its parent. It became a hit and was freely released to the public in 1990 August. However, after the initial release, there was reporting of bugs that the developers would fix occasionally, and hence there were many changes from the initial release. This made John Cristy release version 4.2.9 by the mid-1990s.

Imagemagick version 5 was developed when the user interface was made more friendly to beginners. More scripts and algorithms were included in the user interface functionalities. Version 5 made users to transfer scripts and algorithms from other languages and use them in Imagemagick. Though Imagemagick was developed in C, the enhancements and modules were developed in C++, and it is called Magick++. Several functionalities such as module loader, file identification, and test suites were added to Imagemagick using C++.

Imagemagick had changed its look and form in version 5. Going forward from version 5, a bug was found in the command line where if the users had many images to manage, it looks bulky and confusing. It became important to fix the command line as most users work with the command-line interface than the application’s user interface. The scripts used were mostly BASH and Perl that made necessary changes to the command line, which made the impossible possible by creating canvas in the command line interface. Initially, batch scripts were used that made the work easy in Windows, but it was difficult to use in Linux and other operating systems. So, windows batch scripts were modified to PHP scripts, and Bash scripts were introduced for other operating systems.

Version 6 also made it possible to use any scripts on the command line interface comfortable for the users and make it work on the functionalities. This works only for a single image at a time, and the user must create the API if he/she is developing in their own scripting language. We can also generate scripts by inputting images into the application. We can generate a text file, and the application produces images of the same on the web page. This helps to download the images directly from the application. It should be noted that images have different formats and hence browser support is necessary to get the image in the desired format. Imagemagick changes the font to Arial or Times New Roman font without any warning if the required font is not present.

Different versions of Imagemagick 6 saw changes in command line scripts mainly in the form of geometry, blurs, sharpening images, color changes, edging of images, and noise removal in the images. Furthermore, in addition to the C++ wrapper in Imagemagick, a .NET wrapper was provided in this version that helps users to make enhancements in their application either with C++ or .NET.

We have only versions from 6 available on the website, which was released in 2024. Previous releases are archived, and version 6 are legacy releases that can either be kept by the user or updated to the newer version. We can download these versions from the index of the Imagemagick webpage and use it further for any document creation. The versions available are 6.5, 6.6, 6.7, 6.8, 6.9, and the subversions of the same are available for the users to download and use for raster image editing.

In addition to RGBA images, CMYK and CMYKA images are also supported in newer versions of Imagemagick. For example, colorspaces and pixel channel support is provided in Imagemagick version 7 with any arbitrary images provided by the user, or the application takes an arbitrary version by itself. Hence, the support is provided to arbitrary Colorspaces where pixel channels are stored as floats, and hence the band values are rounded off, ignoring the error.

We have both 64-bit and 32-bit versions for each release of Imagemagick. 7.0.10 version was released in January, and the most recent 7.1.0 was released in August. Whenever any bugs are found, new version updates are released by the Imagemagick team, making users work with the most recent updates always. Major updates come with the release change in numbers, and this change will be published on the website. If the user prefers to go with the older version, they can download the same from the website and use it without making any updates to the software. Changes in scripts and images can be made either via the command line or user interface so that image modifications and color addition can be done through commands without seeing the images.

Recommended Articles

This is a guide to Imagemagick version. Here we discuss the different versions for Imagemagick in detail for better understanding and also the step to install it. You may also have a look at the following articles to learn more –

Commonly Asked Dbms Interview Questions

To familiarize you with the kind of questions that may be asked during a job interview pertaining to the Database Management System, we will explore the most crucial DBMS Interview Questions in this post (DBMS).

Q1)What are some uses for DBMS?

The acronym DBMS, or database management system, stands for an application system whose primary function is around data. This system enables the user to design, save, retrieve, and update data as well as information about the data as it is stored in the database.

Q2)What does the term “database” refer to?

Simply said, a database is a collection of data that has been arranged so that users can access, manage, and submit the data with ease.

The following are some of the main benefits of DBMS

Controlled Redundancy − DBMS enables a way to prevent duplicate data from being saved since all data is kept in a single database, which eliminates redundancy within the database.

Data sharing − Since the same database will be shared by all of the users and by various application programs, data sharing between several users at once is also possible with DBMS.

Backup and Recovery Facility − By offering a function known as “backup and recovery,” which automatically generates the data backup and restores the data as needed, DBMS lessens the agony of producing the backup of the data repeatedly.

Application of Integrity Constraints − Integrity Constraints must be applied to the data in order for the refined data to be saved in the database and to be processed by DBMS.

Independence of Data − Data independence basically implies that you may modify the data’s structure without altering the design of any underlying application applications.

Q4) Why is normalization used in DBMSs?

The analysis of relational schemas that are based on their unique functional dependencies and primary keys in order to satisfy specific criteria is known as normalization.

The attributes consist of:

to reduce the data’s redundancy.

in order to reduce Insert, Delete, and Update Anomalies

Q5)What are the different categories of languages that the DBMS supports?

In the DBMS, there are basically three different kinds of languages, as follows

DDL − Data Definition Language, or DDL, is a collection of SQL queries, such as CREATE, ALTER, TRUNCATE, DROP, and RENAME, that are used to describe the database and schema structure.

DCL − Data Control Language (DCL): DCL is a series of SQL queries, such as GRANT and REVOKE, that are used to manage user access to databases.

DML − Data Manipulation Language, or DML, is used to do database manipulations including inserting, deleting, and updating data using a series of SQL queries such as select, insert, delete, and update.

Q 6) Why is SQL used?

The acronym SQL stands for Structured Query Language, and it is used to interact with relational databases by entering, updating, and/or changing data.

Q 7) Explain the concepts of a Primary key and a Foreign Key.

In a database table, a primary key is used to uniquely identify each record, whereas a foreign key—a specific field or set of fields in one table that serves as the primary key for another table—is primarily used to connect two or more tables together.

Q8) What are the primary distinctions between the Unique Key and the Primary Key?

A few variations are shown below:

The primary distinction between a primary key and a unique key is that a primary key can never contain a null value, but a unique key can.

There can be more than one unique key in a table, but there can only be one main key per table.

Q9)What does the phrase “sub-query” mean in relation to SQL?

Answer − A sub-query is essentially a query that is contained within another query; it is also referred to as an inner query because it is contained within the outer query.

Q 10) What is the use of the DROP command and what are the differences between DROP, TRUNCATE and DELETE commands?

A table, database, index, or view can be dropped or deleted from the database using the DDL command “DROP.”

There are three main distinctions between the DROP, TRUNCATE, and DELETE commands:

Tables may be deleted from the database using the DDL commands DROP and TRUNCATE, and once a table is destroyed, all associated rights and indexes are likewise erased. These 2 procedures cannot be undone, thus they should only be utilized when absolutely required.

On the other hand, the DELETE command is a DML Command that may also be used to delete rows from a table.

Q 11) What is the main difference between UNION and UNION ALL?

When joining data from two or more tables, UNION and UNION ALL are used; UNION eliminates duplicate rows and selects the distinct rows after merging the data from the tables; UNION ALL does not do this; it just selects all the data from the tables.

Q12)Explain the idea of ACID characteristics in DBMS?

The combination of Atomicity, Consistency, Isolation, and Durability traits is known as ACID attributes. These characteristics make it possible for several people to share data in a safe and secure manner.

Atomicity − This is based on the idea of “either all or nothing,” which essentially implies that if a database change occurs, it should either be accessible to everyone besides the user and application program or it shouldn’t be accessible to anybody besides them at all.

Consistency − This guarantees that the database’s consistency is preserved both during and following any internal transactions.

Isolation − As the name implies, this feature specifies that each transaction that takes place is isolated from others. For example, a transaction that has started but has not yet finished should be isolated from others so that other transactions are not influenced by it.

Durability − This feature specifies that the data should always be in a durable state, meaning that any committed data should be accessible in the same state even if the system has a failure or restarts.

Q 13: What Does a DBMS Correlated Subquery Do?

A subquery is sometimes referred to as a nested query or a query that is written inside another query. A subquery is referred to as correlating when it is done for each row of the outer query.

An illustration of a non-related subquery is −

SELECT * from EMP WHERE 'AJITESH' IN (SELECT Name from DEPT WHERE EMP.EMPID=DEPT.EMPID);

In this case, the inner query is not run for every row of the outer query.

Q 14) Explain Entity, Entity Type, and Entity Set in DBMS.

An entity is anything, place, or object that exists independently in reality and whose details may be saved in a database. For instance, any individual, book, etc.

An entity type is a group of entities with similar properties. As an illustration, the STUDENT table comprises rows, each of which is an entity storing the name, age, and student ID of the students. As a result, STUDENT is an entity type that has entities with the same properties.

A grouping of entities that have the same type is an entity set. An example would be a group of a company’s employees.

Q 15) What are the different levels of abstraction in the DBMS?

In the DBMS, there are three layers of data abstraction.

They consist of

Physical Level − The physical level describes how the data is kept in the database and is the lowest level of data abstraction.

Logical Level − The following level of data abstraction, known as the logical level, describes the kind of data and the connections between the data that are kept in the database.

View Level − The highest level of data abstraction, known as the view level, only displays or states a portion of the database.

Q16)What integrity guidelines are there in the DBMS?

The DBMS has two main integrity rules, to be precise.

As follows

Entity Integrity: Declares a crucial principle that a primary key’s value can never be NULL

Referential Integrity: According to this rule, a foreign key’s value must either be NULL or it must serve as the primary key for every other relation.

Q 17) What is the E-R model in the DBMS?

In the DBMS, the E-R model is referred to as an Entity-Relationship model since it is built on the idea of entities and the relationships that exist between them.

Q18) What does a DBMS functional dependence mean?

The relationship between the various qualities of a relation may be described by this constraint, in essence.

Q19)What is 1NF in the DBMS stand for?

The First Normal Form, or 1NF, is the correct response.

The domain of an attribute should only have atomic values in this kind of normalization, which is the simplest. The purpose of this is to eliminate any duplicate columns from the table.

Q20)What does the DBMS’s 2NF stand for?

The Second Normal Form, or 2NF.

Any table that meets the following two requirements is considered to have in the 2NF:

A table is in the 1NF.

A table’s non-prime attributes are considered to be completely functionally dependent on its main key.

Q21)What is 3NF in the DBMS stand for?

The Third Normal Form, or 3NF.

Any table that meets the following two requirements is said to have in the 3NF:

A table is in the 2NF.

It is argued that every non-prime attribute in a table is non-transitively reliant on every table key.

Q22)What is BCNF in the DBMS, question #22?

The Boyce Codd Normal Form, which is tighter than the 3NF, is known as BCNF.

Any table that meets the following two requirements is said to have in the BCNF:

A table is in the 3NF.

Q23)What does a CLAUSE mean in relation to SQL?

This is used in conjunction with SQL queries to get specified data based on user needs and SQL-defined constraints. This is particularly useful for selecting certain records from the entire set of records.

As an illustration, there are queries with the WHERE condition and those with the HAVING clause.

Q24)How can the alternative records from the table in SQL be retrieved?

Answer − The following search may be used to retrieve odd numbers

SELECT EmpId from (SELECT rowno,EmpId from Emp) WHERE mod(rowno,2)=1;

The following query may be used to retrieve the even numbers −

SELECT EmpId from (SELECT rowno,EmpId from Emp) WHERE mod(rowno,2)=0; Q 25) How does SQL handle pattern matching?

The LIKE operator in SQL makes it feasible to match patterns.

When the LIKE operator matches 0 or more characters, it uses the character “%,” and when it matches only one, it uses the character ” .”

Example SELECT * from Emp WHERE name like 'b%'; SELECT * from Emp WHERE name like 'hans_'; Q26)What does a join in SQL mean?

A join is a type of SQL statement that is used to combine data or rows from two or more tables based on a shared field or column.

Q27)What various kinds of SQL joins are there?

There are four different kinds of SQL joins.

Inner Join − This kind of join is used to retrieve information from tables that are shared by both tables.

Left Join − This only returns the matching rows from the table on the right side of the join, returning all the rows from the table on the left of the join.

Right Join − This just returns matching rows from the table on the left of the join, not all the rows from the table on the right of the join.

Full Join − This retrieves all the rows from every table that the join condition has been applied to, and the rows that do not match have null values.

Q28) What does the term “trigger” mean?

The answer is that a trigger is one of the crucial scripts or programs that are automatically run in response to events occurring in a table or view. As an illustration, whenever a new record is added to an employee database, the data is automatically produced in the relevant tables, such as the roles, departments, and compensation tables.

Q29) What is the Stored Procedure? (Question 29)

A stored procedure is a collection of SQL statements organized into a function that is saved in relational database management systems (RDBMS) and accessible whenever necessary.

Q30)What is RDBMS, question 30?

RDBMS stands for Relational Database Management System. It is a database management system that accesses data by using common fields found in different tables.

Q31)What various kinds of associations does the DBMS support?

In DBMS, relationships show how the tables are related to one another.

Various kinds of relationships include

One-to-One − This essentially indicates that there should be one record in each table, or a one-to-one relationship, between the tables. For instance, a married couple is only permitted to have one spouse each.

One-to-Many − A primary key table has just one record, although there may be many, one, or no records in the linked table according to the one-to-many connection theory. A mother could have a lot of kids.

Many-to-Many − According to this, both tables may be connected to several other tables. Example: Siblings can be many and often are.

Learn The Different Examples Of Plsql Pivot

Introduction to PLSQL pivot

PL/SQL provides the different types of functionality to the user; the pivot is the one type of functionality that is provided by the PL/SQL. Basically, we call it a pivot clause. By using pivot clauses, we cross table query as per requirement; in another way, we can combine, or we can aggregate our result from rows into the columns as per our requirement. Basically, the pivot clause introduced in Oracle 11g and the pivot returns more than one column after the execution. By using pivot clauses, we can combine the difference into a single result and generate the required output.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Syntax select * from (select colm name 1, colm name name N from specified table where conditional expression) PIVOT

Explanation

First, we use the select clause to retrieve the records from the specified table. Inside the select, we write the subquery with a column name, and here we specify where clause with conditional expression.

Specified aggregate function name: it is used to specify the aggregate function name that we can write any function name such as SUM, MIN, MAX, etc.

IN (specified expression 1, specified expression 2,…..specified expression N): It is used to hold the list of column name 2 that values to pivot into the cross table.

Specified subquery: Basically, it is used instead of list values, and the output of the subquery would be utilized to calculate the values for column 2 in the cross-tabulation query output, which would then be translated to headings in this syntax.

How does pivot work in PL/SQL?

Now let’s see how pivot clauses work in PL/SQL as follows.

Let’s see the different ways to implement the pivot clause as follows.

Specify the Group Columns:

Any columns not stated in the FOR clause are utilized as a part of the Oracle PIVOT GROUP BY when employing the PIVOT keyword. The only column in the previous example was the location column, which was fine.

We can also use the Where clause with pivot clause:

The results of our searches above provide a pivoted summary of all data. A few fields are used to aggregate all of the entries, and the SUM of the selling amount is displayed.

What if you want to limit it to just a few rows?

Like a regular SELECT query, you may use a WHERE clause. Then we will get the error due to incorrect syntax, so we need to write the correct syntax that means the PIVOT clause must appear after the WHERE clause; this is the case.

Now let’s see how we can use Aliasing in the pivot column:

The column headings will be shown as the table’s column name in the queries we’ve looked at so far. What if you want to call them something else? A column alias can be specified using the PIVOT keyword. Both the pivot clause and the pivot in a clause can be used for this.

Now let’s see how we can perform the multiple aggregations in pivot:

We can make the group of multiple columns:

This is another way to implement the multiple columns into the pivot statement to group by multiple columns as per our requirement.

We can implement pivot with XML as follows:

You may display your findings in an XML format using the PIVOT keyword. It’s as simple as following the PIVOT keyword with the XML keyword.

Examples of PLSQL pivot

Now let’s see the different examples of pivot clauses in PL/SQL for better understanding as follows.

First, we need to create the table by using the following create table statement as follows.

create table stud(roll_no integer not null, name varchar2(50), dept_id integer not null, primary key(roll_no));

Explanation

By using create table statement, we created a new table name as a stud with different attributes such as roll_no, name, and dept_id with different data types, and in this example, the primary key is roll_no. The final output of the above statement we illustrated by using the following screenshot as follows.

insert into stud(roll_no, name, dept_id) values(1,'Jenny',10); insert into stud(roll_no, name, dept_id) values(2,'Jenny',10); insert into stud(roll_no, name, dept_id) values(3,'Jenny',20); insert into stud(roll_no, name, dept_id) values(5,'Sameer',20); insert into stud(roll_no, name, dept_id) values(6,'Sameer',10); insert into stud(roll_no, name, dept_id) values(7,'Sameer',20);

Explanation

In the stud table, we inserted a total of 6 records by using the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

If records are not in order, then we can use order by clause to make the records in order. In this example, all records we order by roll_no, as shown in the above screenshot.

Now implement the pivot clause that means write the cross table subquery as follows.

select * from (select name, dept_id from stud) pivot (count(dept_id) for dept_id IN (10, 20, 30)) order by name;

Explanation

In the above example, we write the two different queries that we call subquery and merge by using the pivot clause. Now let’s see how it works. In the above example, we first decide which field we want to add in the pivot clause; here, we add name and dept_no. After that, we need to specify the column in any order that we want. The next part of the query, it contains the aggregate function and pivot value that we want, as shown in the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

Conclusion – PLSQL pivot

We hope from this article you learn PL/SQL pivot. From the above article, we have learned the basic syntax of the pivot, and we also see different examples of the pivot. From this article, we learned how and when we use PL/SQL pivot.

Recommended Articles

We hope that this EDUCBA information on “PLSQL pivot” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

Update the detailed information about 6 Commonly Used Different Kinds Of Sql Constraints on the Achiashop.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!