#codEasy

 In DBMS, aggregate functions are used to combine multiple values of rows to get a single meaningful value.  Various Aggregate functions are: Count(): Count(*): Returns the total number of records Count(column_name): Returns the total number of non-null values in the column. Count(distinct column_name): Returns the total number of distinct non-null values in the column. Sum(): Sum(column_name): Sum of all non-null values in the column Sum(distinct column_name): Sum of all distinct non-null values in the column Avg(): Avg(salary) = Sum(salary) / count(salary) = 310/5 Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4   Min(): Min(salary): Minimum value in the salary column except NULL i.e., 40. Max(salary): Maximum value in the salary i.e., 80.

In Nested Queries , a query is written inside a query. The result of inner query is used in the execution of the outer query. There are mainly two types of nested queries: Independent Nested Queries: In independent nested queries, query execution starts from inner query to outer query. The execution of inner query is independent of the outer query, but the result of inner query is used in the execution of the outer query.                            For Example: select S_NAME from STUDENT where S_ID IN                                                                                                                                       (select S_ID from STUDENT_COURSE where C_ID IN                                                                                                                           (select C_ID from COURSE where C_NAME='DSA' or C_NAME='DBMS')); Co-Related Nested queries: In co-related nested query, the output of the inner query depends on the row which

A Procedure is simply a block which performs one or more specific tasks.  A procedure contains a header and a body: Header: The header contains name of the procedure and the variables passed to the procedure. Body: The body contains a declaration section, execution section and exception section. We can pass the parameters in the procedure in three ways: IN parameter: It can be referenced by procedure or function. The value of the parameters can't be overwritten by the procedure or the function. OUT parameter: It can't be referenced by procedure or function. The values of the parameters can be overwritten by the procedure or the function. INOUT parameter: It can referenced by the procedure or function. The values of the parameters can be overwritten by the procedure or function. Syntax: CREATE Procedure_name     [parameters] IS     [declaration section] BEGIN     [executable section] [EXCEPTION     Exception_section] END[procedure_name] Example: create procedure "INSER

 The Client-Server model is a distributed architecture system that divides the task between the servers and clients. Here, when the client sends a request to the server through the internet, the server accepts the requested process and delivers the requested data packets back to the client.  The browser interacts with server as: User enters the URL(Uniform Resource Locator) of the website. The browser then requests the DNS (Domain Name System) server. DNS Server looks up for the address of the WEB Server. DNS Server responds with the IP Address of the WEB Server. Browser sends over an HTTP/HTTPS request to the WEB Server's IP Server sends over the necessary files of the websites. Browsers then renders the files and website is displayed. Advantages: Centralized System Cost Efficient Data recovery Capacity of client and servers can be changed Disadvantages: Clients are prone to virus if present in server or uploaded into the server Servers are prone to Denial of Service (DOS) attacks

Specialization, Generalization, Aggregation in ER Model: Generalization: It is the process of extracting common properties from a set of entities and create generalized entity from it. It is a bottom-up approach in which two or more entities can be generalized to a higher entity, if they some attributes in common. For Example, STUDENT and FACULTY can be generalized to a higher level entity called PERSON. Specialization: Here, an entity is divided into sub entities based on characteristics. It is a top-down approach where higher level entities can be divide into two or more lower level entities. For example, EMPLOYEE entity in an employee management system, can be specialized into DEVELOPER or TESTER.  Aggregation: An ER Diagram is not capable of representing relationship between an entity and a relationship which may be required in some scenarios. In those cases, a relationship with corresponding entities is aggregated into a higher level entity. Aggregation is an abstraction through w

Advantages of DBMS over FILE System Data Redundancy and inconsistency: Redundancy is the concept of repetition of data. Each data may have more than a single copy. The file system cannot control the data as each user keeps the data to run different applications. If two users are maintaining the data of the same file to run different applications, then if one users changes the data, it doesn't reflect in files used by the other users which can lead to inconsistency of the data. DBMS controls redundancy by maintaining a single repository of data that is defined once and accessed by many. Data sharing: The file system doesn't allow to share the data and sharing is too complex. DBMS allows easy sharing of data due to centralized system. Data concurrency: Concurrent access to data means, more than one user accessing the data. Anomalies occur because changes made by a user can be lost because of the changes made by the other user. The file system doesn't provide any facilities to

THREE LEVELS OF DATA INDEPENDENCE It divides the entire system into three inter-related but independent modules: Physical Level: Here, information about the location of database objects in the data store is kept. Various DBMS users are unaware of the locations of these objects. The Physical level of database describes how the data is stored in secondary storage devices such as disks and tapes. Conceptual Level: Here, data is represented in the form of various tables. For Example, STUDENT database may contain NAME and COURSE tables, but the users are unaware of their storage. It refers to what kind of data to be stored in a database. External Level: Here, it specifies the view of data in terms of conceptual level tables. It can generate different views of the database for different users. It focuses main on data abstraction.  Data Independence: It means change of data at one level must not affect the another level. Physical Data Independence: Any change in the physical location of the t

  SQL A  Database  is a collection of data which is organized for easy access and efficient management and updating. It is made of  tables  that store the relevant information. Database Tables A  table  stores data in an organized way consisting of rows and columns similar to the one which we see in Microsoft Excel. Database often contains multiple tables each created and designed for a specific purpose. For example we create a table of Patient Names and their contact details in a particular hospital.  Each  table  has own set of fields based on the data it can store.  A Table can have specified number of columns but can have any number of rows. Primary Key A Primary key is a field in the table that uniquely defines the table records. It must contain a unique value for each row and must not contain  NULL  values.  For Example , The column which contains the value ID would be a good choice for a primary key in the table.  Tables are limited to ONE primary key each and its value must be

How to analyze Time Complexity?                        Running time of an algorithm depends upon multiple factors: Single vs Multiple Processors(It is using a single processor, then we cannot run our program parallelly) Read and write speed of the program to the memory or the disk. 32-bit vs 64-bit architecture Configuration of the machine Input Consider a Model Machine with: Single Processor 32 bit Sequential Execution 1 unit of time for arithmetic and logical operations 1 unit of time for assignment and return statements Let's define a function which calculates the sum of two numbers: sum(n1, n2):     return (a+b) Now, we know that 1 unit of time is taken for arithmetic operations and 1 unit of time for return statement. Therefore, irrespective of the inputs, the above program is executed in two units of time. Let's define a function which calculated the sum of the list: sum(list, n):     total = 0                ---> this line takes 1 unit of time ---> execute one time