SQL

what is interface

An interface is a reference type in Java. It is similar to class. It is a collection of abstract methods. A class implements an interface, thereby inheriting the abstract methods of the interface.

what are joins in SQL and there types?

Here are the different types of the JOINs in SQL: (INNER) JOIN: Returns records that have matching values in both tables. LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table. ... FULL (OUTER) JOIN: Return all records when there is a match in either left or right table.

34. difference between call by and call by reference

Image result for difference between call by and call by referencewww.javawithus.com Difference between Call by Value and Call by Reference. In call by value, a copy of actual arguments is passed to formal arguments of the called function and any change made to the formal arguments in the called function have no effect on the values of actual arguments in the calling function.

what are the acid property of database?

In computer science, ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties of database transactions intended to guarantee validity even in the event of errors, power failures, etc.

what are aggregate functions?

In database management an aggregate function or aggregation function is a function where the values of multiple rows are grouped together to form a single summary value. Common aggregate functions include : Average (i.e., arithmetic mean) Count.

What is Tuple in Table?

In the context of relational databases, a tuple is one record (one row). The information in a database can be thought of as a spreadsheet, with columns (known as fields or attributes) representing different categories of information, and tuples (rows) representing all the information from each field associated with a single record.

how will you design a database from scratch

Take a look at the description and highlight all nouns. The nouns in the description can roughly be divided into three categories: tables, attributes, and examples. Tables represent primary entities in the system: people, physical objects, events, transactions, etc. Attributes are properties associated with a primary entity. They describe features of your entity. In the database model they will be the columns in your tables. Examples are just that, examples. They help you understand the datatypes of certain attributes and they help you understand the relationship between different entities.

what is normalization?

Normalization is the process of reorganizing data in a database so that it meets two basic requirements: (1) There is no redundancy of data (all data is stored in only one place), and (2) data dependencies are logical (all related data items are stored together). Normalization is important for many reasons, but chiefly because it allows databases to take up as little disk space as possible, resulting in increased performance. Normalization is also known as data normalization.

Explain different types of joins- inner, outer, self and cross?

A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each. ANSI-standard SQL specifies five types of JOIN : INNER , LEFT OUTER , RIGHT OUTER , FULL OUTER and CROSS . As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.

what are constructors and destructors

Constructors and Destructors in C++ Constructors are special class functions which performs initialization of every object. ... Constructors initialize values to object members after storage is allocated to the object. Whereas, Destructor on the other hand is used to destroy the class object.

what are the constraints in SQL and their types?

SQL Constraints SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table. The following constraints are commonly used in SQL: NOT NULL - Ensures that a column cannot have a NULL value UNIQUE - Ensures that all values in a column are different PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table FOREIGN KEY - Uniquely identifies a row/record in another table CHECK - Ensures that all values in a column satisfies a specific condition DEFAULT - Sets a default value for a column when no value is specified INDEX - Used to create and retrieve data from the database very quickly

what are different types of statement in SQL

Type of SQL Statement (DDL, DML, DCL, TCS, SCS Commands) SQL statements are divided into five different categories: Data definition language (DDL), Data manipulation language (DML), Data Control Language (DCL), Transaction Control Statement (TCS), Session Control Statements (SCS).

Characteristics of Object oriented programming?

What are the characteristics of Object Oriented programming language? Encapsulation - Encapsulation is capturing data and keeping it safely and securely from outside interfaces. Inheritance- This is the process by which a class can be derived from a base class with all features of base class and some of its own. This increases code reusability. Polymorphism- This is the ability to exist in various forms. For example an operator can be overloaded so as to add two integer numbers and two floats. Abstraction- The ability to represent data at a very conceptual level without any details.

what are different types of attributes?

What are the types of attributes in dbms? Single valued Attributes : An attribute, that has a single value for a particular entity. For example, age of a employee entity. Multi valued Attributes : An attributes that may have multiple values for the same entity.

what do you know about sub-queries?

subquery is a SQL query nested inside a larger query. A subquery may occur in : - A SELECT clause - A FROM clause - A WHERE clause The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL. A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select. The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query.

what is database?

A database is a collection of information that is organized so that it can be easily accessed, managed and updated.

why do we use primary in table

A primary key is used to ensure data in the specific column is unique. You can only set constraints with primary keys, by setting a foreign key to another column which creates a relationship with the column that has the primary key set. A prime use of a primary key is in the case of a users table.

what is view

A view is a subset of a database that is generated from a query and stored as a permanent object. Although the definition of a view is permanent, the data contained therein is dynamic depending on the point in time at which the view is accessed.

what is an ER diagram

An Entity Relationship Diagram shows entities (tables) in a database and relationships between tables within that database. For a good database design it is essential to have an Entity Relationship Diagram. There are three basic elements in ER-Diagrams: Entities are the "things" for which we want to store information.

how may null values are allowed in unique key and why?

As you know, when you create a UNIQUE constraint on a nullable column, SQL Server allows only one NULL value, thereby maintaining the UNIQUEness. However, there are situations when we need more than one NULL value in the column but still have to maintain uniqueness, ignoring all those NULL values.

what is the difference between primary and unique key?

Behavior: Primary Key is used to identify a row (record) in a table, whereas Unique-key is to prevent duplicate values in a column (with the exception of a null entry). 2. Indexing: By default SQL-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key.

what is default constraint?

Default constraints are a special case of column defaults. A column default is some value or function that the column will take when an INSERT statement doesn't explicitly assign a particular value. In other words, the column default is what the column will get as a value by default.

what is an encapsulation, polymorphism, abstraction, inheritance?

Encapsulation is one of the four fundamental OOP concepts. The other three are inheritance, polymorphism, and abstraction. Encapsulation in Java is a mechanism of wrapping the data (variables) and code acting on the data (methods) together as a single unit. ... Therefore, it is also known as data hiding.

what are different types of normal forms

Normalization Here are the most commonly used normal forms: First normal form(1NF) Second normal form(2NF) Third normal form(3NF) First normal form (1NF) As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It should hold only atomic values. Second normal form (2NF) A table is said to be in 2NF if both the following conditions hold: Table is in 1NF (First normal form) No non-prime attribute is dependent on the proper subset of any candidate key of table. An attribute that is not part of any candidate key is known as non-prime attribute. Third Normal form (3NF) A table design is said to be in 3NF if both the following conditions hold: Table must be in 2NF Transitive functional dependency of non-prime attribute on any super key should be removed. An attribute that is not part of any candidate key is known as non-prime attribute. In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional dependency X-> Y at least one of the following conditions hold: X is a super key of table Y is a prime attribute of table An attribute that is a part of one of the candidate keys is known as prime attribute.

Difference between order by and group by?

ORDER BY alters the order in which items are returned. GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc). ORDER BY: sort the data in ascending or descending order.

what is an OOPs?

Object-oriented programming (OOP) is a programming language model organized around objects rather than "actions" and data rather than logic. Historically, a program has been viewed as a logical procedure that takes input data, processes it, and produces output data.

Difference between delete, truncate and drop

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

why do we need foreign keys?

The primary purpose of the foreign key constraint is to enforce referential integrity and improve performance, but there are additional benefits of including them in your database design. To better understand the concept of the foreign key, you must understand the different relationships found in a relational database.

what are access specifier? - public private and protected

These are the access specifier, i.e. it specifies the accessibility. One can access a private member within the same class in which it is described only. One can access a protected member within all classes in the same package and within the subclasses in other package. Public members can be accessed by any class.

what is the difference between the WHERE and HAVING clauses?

WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL. 3) One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause

What is DDL, DML, DQL in SQL?

When SQL is used to create, modify, or destroy objects within an RDBMS, it puts on its Data Definition Language (DDL) hat. Here you have the CREATE, ALTER, and DROP statements, plus a couple of others. The Data Manipulation Language (DML) is the domain of INSERT, UPDATE, and DELETE, which you use to manipulate data. Some bundle the Data Query Language (DQL) into DML, arguing that it also manipulates data. There are merits to this argument, not least that there is but a single member in this category: the SELECT statement.

what is index

An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned. In SQL Server, a clustered index determines the physical order of data in a table.

why we can have one Null value unique key?

As you know, when you create a UNIQUE constraint on a nullable column, SQL Server allows only one NULL value, thereby maintaining the UNIQUEness. However, there are situations when we need more than one NULL value in the column but still have to maintain uniqueness, ignoring all those NULL values.

what is the difference between UDE and SP

Basic Differences between Stored Procedure and Function in SQL Server. ... Even a procedure can return zero or n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters . Functions can be called from Procedure whereas Procedures cannot be called from a Function.

difference between method overloading and method overriding

Image result for difference between method overloading and method overriding Overloading occurs when two or more methods in one class have the same method name but different parameters. Overriding means having two methods with the same method name and parameters (i.e., method signature). One of the methods is in the parent class and the other is in the child class.

What is primary key, UNIQUE KEY, FOREIGN KEY, trigger, Index?

PRIMARY KEY is a UNIQUE INDEX that is the identifier for any given row in the table. As such, it must not be null, and is saved as a clustered index. UNIQUE INDEX is an index in which each row in the database must have a unique value for that column or group of columns. This is useful for preventing duplication, e.g. for an email column in a users table where you want only one account per email address. FOREIGN KEY is a reference to a column in another table. It enforces Referential Integrity, which means that you cannot create an entry in a column which has a foreign key to another table if the entered value does not exist in the referenced table. A database trigger is special stored procedure that is run when specific actions occur within a database. Most triggers are defined to run when changes are made to a table's data. INDEX is similar to the index at the back of a book. It provides a simplified look-up for the data in that column so that searches on it are faster.

what are the properties of the relational table

Properties of Relational Tables Relational tables have six properties: Values Are Atomic This property implies that columns in a relational table are not repeating group or arrays. Such tables are referred to as being in the "first normal form" (1NF). The atomic value property of relational tables is important because it is one of the cornerstones of the relational model. The key benefit of the one value property is that it simplifies data manipulation logic. Column Values Are of the Same Kind In relational terms this means that all values in a column come from the same domain. A domain is a set of values which a column may have. For example, a Monthly_Salary column contains only specific monthly salaries. It never contains other information such as comments, status flags, or even weekly salary. This property simplifies data access because developers and users can be certain of the type of data contained in a given column. It also simplifies data validation. Because all values are from the same domain, the domain can be defined and enforced with the Data Definition Language (DDL) of the database software. Each Row is Unique This property ensures that no two rows in a relational table are identical; there is at least one column, or set of columns, the values of which uniquely identify each row in the table. Such columns are called primary keys and are discussed in more detail in Relationships and Keys. This property guarantees that every row in a relational table is meaningful and that a specific row can be identified by specifying the primary key value. The Sequence of Columns is Insignificant This property states that the ordering of the columns in the relational table has no meaning. Columns can be retrieved in any order and in various sequences. The benefit of this property is that it enables many users to share the same table without concern of how the table is organized. It also permits the physical structure of the database to change without affecting the relational tables. The Sequence of Rows is Insignificant This property is analogous the one above but applies to rows instead of columns. The main benefit is that the rows of a relational table can be retrieved in different order and sequences. Adding information to a relational table is simplified and does not affect existing queries. Each Column Has a Unique Name Because the sequence of columns is insignificant, columns must be referenced by name and not by position. In general, a column name need not be unique within an entire database but only within the table to which it belongs.

What is 3rd normal form?

The third normal form (3NF) is a normal form used in database normalization. ... Codd's definition states that a table is in 3NF if and only if both of the following conditions hold: The relation R (table) is in second normal form (2NF) Every non-prime attribute of R is non-transitively dependent on every key of R.