SQL & NOSQL QUERIES

-
SQL QUERIES

query to fetch unique values
SELECT DISTINCT MAJOR from STUDENT; 
or
SELECT MAJOR FROM STUDENT GROUP BY(MAJOR);

SELECT Major, COUNT(*) as TOTAL_COUNT FROM Student WHERE MAJOR = 'Computer Science';

SELECT MAJOR, COUNT(MAJOR) from Student group by MAJOR order by COUNT(MAJOR) DESC;

SELECT * from Student ORDER BY GPA DESC LIMIT 5;

Print the first 3 characters of FIRST_NAME from Student table:
SELECT SUBSTRING(FIRST_NAME, 1, 3)  FROM Student;

SQL query to find the position of alphabet (‘a’) int the first name column ‘Shivansh’ from Student table:
SELECT INSTR(FIRST_NAME, 'a') FROM Student WHERE FIRST_NAME = 'Shivansh';

SQL query that fetches the unique values of MAJOR Subjects from Student table and print its length:
SELECT MAJOR,LENGTH(MAJOR) FROM Student GROUP BY(MAJOR;         
or                                                                                          
SELECT DISTINCT MAJOR, LENGTH(MAJOR) FROM Student;  

SQL query to print all Student details from Student table order by FIRST_NAME Ascending and MAJOR Subject descending:
SELECT * FROM Student ORDER BY FIRST_NAME , MAJOR DESC;

SQL query to print details of the Students whose FIRST_NAME ends with ‘a’:
SELECT * FROM Student WHERE FIRST_NAME LIKE '%a';

SQL query to print details of the Students whose FIRST_NAME ends with ‘a’ and contains six alphabets.
SELECT * FROM Student WHERE FIRST_NAME LIKE '_____a';

SQL query to print details of the Students whose GPA lies between 9.00 and 9.99.
SELECT * FROM Student WHERE GPA BETWEEN 9.00 AND 9.99;

SQL query to fetch the count of Students having Major Subject ‘Computer Science’.
SELECT Major, COUNT(*) as TOTAL_COUNT FROM Student WHERE MAJOR = 'Computer Science';

SQL query to fetch the no. of Students for each MAJOR subject in the descending order.
SELECT MAJOR, COUNT(MAJOR) from Student group by MAJOR order by COUNT(MAJOR) DESC;

SQL query to show only odd rows from Student table.
SELECT * FROM Student WHERE student_id % 2 != 0;

SQL query to determine the nth (say n=5) highest GPA from a table.
SELECT * FROM Student ORDER BY GPA DESC LIMIT 5, 1;

SQL query to determine the 5th highest GPA without using LIMIT keyword.
SELECT * FROM Student s1 
WHERE 4 = (
    SELECT COUNT(DISTINCT (s2.GPA)) 
    FROM Student s2
    WHERE s2.GPA >= s1.GPA
);

SQL query to show the second highest GPA from a Student table using sub-query.
SELECT MAX(GPA) FROM Student WHERE GPA NOT IN(SELECT MAX(GPA) FROM Student);

SQL query to fetch the MAJOR subject that have less than 4 people in it.
SELECT MAJOR, COUNT(MAJOR) AS MAJOR_COUNT FROM Student GROUP BY MAJOR HAVING COUNT(MAJOR) < 4;

SQL query to show all MAJOR subject along with the number of people in there.
SELECT MAJOR, COUNT(MAJOR) AS ALL_MAJOR FROM Student GROUP BY MAJOR;

SQL query to show the last record from a table.
SELECT * FROM Student WHERE STUDENT_ID = (SELECT MAX(STUDENT_ID) FROM STUDENT);

SQL query to fetch the first row of a table.
SELECT * FROM Student WHERE STUDENT_ID = (SELECT MIN(STUDENT_ID) FROM Student);

SQL query to fetch the last five records from a table.
SELECT * 
FROM (
    SELECT * 
    FROM Student 
    ORDER BY STUDENT_ID DESC 
    LIMIT 5
) AS subquery
ORDER BY STUDENT_ID;

SQL query to show the current date and time.
Query to get current date : 
  SELECT CURDATE();
Query to get current date and time : 
  SELECT NOW();

SQL query to show the top 3 students with the highest GPA:
  SELECT * FROM Student ORDER BY GPA DESC LIMIT 3;


SQL Indexes
An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer.
Creating an Index
  Syntax
    CREATE UNIQUE INDEX index
    ON TABLE (column1, column2,…..);

SQL Views
Views in SQL are a kind of virtual table.
A view also has rows and columns like tables, but a view doesn’t store data on the disk like a table. 
View defines a customized query that retrieves data from one or more tables, and represents the data as if it was coming from a single source.
  Syntax
    CREATE VIEW view_name AS
      SELECT column1, column2.....
      FROM table_name
      WHERE condition;
Rules:
should be created from a single table.
should not include GROUP BY clause or ORDER BY clause.
should not have the DISTINCT keyword.
should not be created using nested queries or complex queries.


NOSQL QUERIES

Example selects from the inventory collection all documents where the status equals "D":
db.collection('inventory').find({ status: 'D' });

Example retrieves all documents from the inventory collection where status equals either "A" or "D":
db.collection('inventory').find({
  status: { $in: ['A', 'D'] }
});

Specify AND Conditions:
Example retrieves all documents in the inventory collection where the status equals "A" and qty is less than ($lt) 30:
db.collection('inventory').find({
  status: 'A',
  qty: { $lt: 30 }
});

Specify OR Conditions:
db.collection('inventory').find({
  $or: [{ status: 'A' }, { qty: { $lt: 30 } }]
});

Specify AND as well as OR Conditions:
db.collection('inventory').find({
  status: 'A',
  $or: [{ qty: { $lt: 30 } }, { item: { $regex: '^p' } }]
});

Update a Single Document
update the first document where item equals "paper":
await db.collection('inventory').updateOne(
  { item: 'paper' },
  {
    $set: { 'size.uom': 'cm', status: 'P' },
    $currentDate: { lastModified: true }
  }
);

Update Multiple Documents
update all documents where qty is less than 50:
await db.collection('inventory').updateMany(
  { qty: { $lt: 50 } },
  {
    $set: { 'size.uom': 'in', status: 'P' },
    $currentDate: { lastModified: true }
  }
);

await db.collection('inventory').deleteMany({});
await db.collection('inventory').deleteMany({ status: 'A' });
await db.collection('inventory').deleteOne({ status: 'D' });

Comments

Post a Comment

Popular posts from this blog

PL/SQL

-
PL/SQL PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL). PL/SQL can execute a number of queries in one block using single command. One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types, which are stored in the database for reuse by applications. The PL/SQL engine resides in the Oracle engine. The Oracle engine can process not only single SQL statement but also block of many statements. Structure of PL/SQL Block: DECLARE     declaration statements; BEGIN     executable statements EXCEPTIONS     exception handling statements END; / Example : SET SERVEROUTPUT ON; SQL> DECLARE        var varchar2(40) := 'I love GeeksForGeeks' ;      BEGIN        dbms_output.put_line(var);      END;      / PL/SQL Functions PL/SQL functions are reusable blocks of code that can be used to perform specific tasks. They are...
Read more

JAVA8 Features

-
Java8 Features 1) Lambda Expressions : Concise functional code using ->. Lambda Expression basically expresses an instance of the functional interface, Lambda Expression provides a clear and concise way to represent a method of the functional interface using an expression. 2) Functional Interfaces : Single-method interfaces. An interface that contains only one abstract method is known as a functional interface. There is no restriction, that can have n number of default and static methods inside a functional interface. A functional interface is an interface that contains only one abstract method.  @FunctionalInterface annotation is used to ensure that the functional interface can’t have more than one abstract method. Runnable, ActionListener, and Comparable are some of the examples of functional interfaces.   Runnable –> This interface only contains the run() method.   Comparable –> This interface only contains the compareTo() method.   ActionListener –> ...
Read more

Build Automation

-
ANT: Ant was the first build tool released in 2000. Ant was developed on procedural programming idea. Ant was improved with an ability to accept plug-ins and dependency management over the network, with the help on Apache-IVY. Drawbacks: 1) XML is used as a format to write the build scripts. 2) Being hierarchical is not good for procedural programming, and 3) XML is relatively unmanageable.   MAVEN: Maven was introduced in 2004. Maven relied on the conventions and was able to download the dependencies over the network. Benefits : Life cycle of Maven, while following the same life cycle for multiple projects continuously. Problems: 1) Maven does not handle the conflicts between versions of the same library. 2) Complex customised build scripts are difficult to write in Maven, as compared to writing the build scripts in ANT. GRADLE : Gradle came into picture in 2012 Features  Declarative builds and build-by-convention 1) Gradle is available with separate Domain Specific Language ...
Read more