Chetu Interview Question, SQL Group By

SQL: GROUP BY Clause

This SQL tutorial explains how to use the SQL GROUP BY clause with syntax and examples.

Description

The SQL GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

Syntax

The syntax for the SQL GROUP BY clause is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, ... expression_n

Expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause at the end of the SQL statement.

aggregate_function

This is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions.

aggregate_expression

This is the column or expression that the aggregate_function will be used on.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

Example - Using SUM function

Let's look at a SQL GROUP BY query example that uses the SQL SUM function.
This GROUP BY example uses the SUM function to return the name of the department and the total sales (for the department).

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department;

Because you have listed one column (the department field) in your SQL SELECT statement that is not encapsulated in the SUM function, you must use the GROUP BY Clause. The department field must, therefore, be listed in the GROUP BY clause.

Example - Using COUNT function

Let's look at how we could use the GROUP BY clause with the SQL COUNT function.
This GROUP BY example uses the COUNT function to return the department and the number of employees (in the department) that make over $25,000 / year.

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Example - Using MIN function

Let's next look at how we could use the GROUP BY clause with the SQL MIN function.
This GROUP BY example uses the MIN function to return the name of each department and the minimum salary in the department.

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department;

Example - Using MAX function

Finally, let's look at how we could use the GROUP BY clause with the SQL MAX function.
This GROUP BY example uses the MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;

Chetu Interview Question

Difference between Public and Static Constructor

static and public are orthogonal concepts (i.e. they don’t have anything to do with each other).

public simply means that users of the class can call that constructor (as opposed to, say, private).

static means that the method (in this case the constructor) belongs not to an instance of a class but to the “class itself”. In particular, a static constructor is called once, automatically, when the class is used for the first time.

Furthermore, a static constructor cannot be made public or private since it cannot be called manually; it’s only called by the .NET runtime itself – so marking it as public wouldn’t be meaningful.

Static constructor runs just once, before your class is instantiated. It's used if you want something to happen just once. A nice example would be a Bus class (similar to something they explain in MSDN article):

public class Bus

{

    public static int busNo = 0;

    static Bus()

    {

        Console.WriteLine("Woey, it's a new day! Drivers are starting to work.");

    }

    public Bus()

    {

        busNo++;

        Console.WriteLine("Bus #{0} goes from the depot.", busNo);

    }

}

class Program

{

    static void Main(string[] args)

    {

        Bus busOne = new Bus();

        Bus busTwo = new Bus();

    }

    // Output:

    // Woey, it's a new day! Drivers are starting to work.

    // Bus #1 goes from the depot.

    // Bus #2 goes from the depot.

}

Chetu Interview Question

Constructor(s) in a class is/are special methods which get called automatically when an object of a class is created. Constructors are specially used to initialize data members. There are different types of constructors you can write in a class -

1. Default Constructor

2. Parameterized Constructor

3. Copy Constructor

4. Static Constructor

class SampleA

{

public SampleA()

{

Console.WriteLine("Sample A Test Method");

}

}

Default Constructor

When you do not declare any constructor, the class will call its default constructor which has a default public access modifier. The default constructor is a parameter less constructor which will be called by a class object.

Let's see an example of a Default Constructor -

public class Person {     private int m_PersonID;     private string m_FirstName, m_LastName, m_City;     public Person()     {         m_PersonID = 19929;         m_FirstName = "No First Name";         m_LastName = "No Last Name";         m_City = "No City";     } }

This default constructor will be executed whenever the class is initialized – Person p = new Person();

Note: If the class is abstract, then the accessibility of the default constructor is protected. Otherwise, the accessibility for the default constructor is public

Parameterized constructors

Now let’s see parameterized constructors. You can also call it as constructor overloading. Default constructors always initialize the objects with the same values. In case you want to initialize the class with different values, you can use Parameterized constructors.

public class Person {     private int m_PersonID;     private string m_FirstName, m_LastName, m_City;     public Person()     {         m_PersonID = 19929;         m_FirstName = "No First Name";         m_LastName = "No Last Name";         m_City = "No City";     }     public Person(string firstName,string lastName)     {         m_FirstName = firstName;         m_LastName = lastName;     } }

To invoke the parameterized constructor, use this Person p1 = new Person(“DotNet”, “Curry”);

Copy Constructor

Now let's see an example of Copy Constructor. Copy constructor is the parameterized constructor which takes a parameter of the same type. It allows you to initialize a new object with the existing object values.

public class Person {     private int m_PersonID;     private string m_FirstName, m_LastName, m_City;     public Person()     {         m_PersonID = 19929;         m_FirstName = "No First Name";         m_LastName = "No Last Name";         m_City = "No City";     }     public Person(string firstName,string lastName)     {         m_FirstName = firstName;         m_LastName = lastName;     }           //copy constructor     public Person(Person person)     {         m_PersonID = person.m_PersonID;         m_FirstName = person.m_FirstName;         m_LastName = person.m_LastName;         m_City = person.m_City;     } }

Here’s an example:

// Instance constructor. Person p1 = new Person(1, "DotNet", "Curry", "Pune"); // Copy Constructor Person p2 = new Person(p1);

Static Constructors

Static constructor is used to initialize the static data members of the class. Static constructor is only called once while creation of the first instance of the class. After that, no instance of a class will call the static constructor. You can also use static constructor to execute some code of the class which must be executed only once.

public class Person {     static Person()     {         //Static Members     } }

In inheritance, the calling of the constructor starts from the parent class.

Let's see how to use these constructors -

static void Main(string[] args) {     Person p1 = new Person();//This will call Default Constructor     Person p2 = new Person("Pravin", "D");//This will call two parameterized Constructor     Person p3 = new Person(p2);//This will call Copy Constructor }

It is worth mentioning that you can also create a private constructor, which is  generally used in classes that contain static members only. If you create a private constructor, you cannot create an instance of a class.

class Person {     // Private Constructor:     private Person() { }           ... }

I hope you got an idea of the different types of Constructors in C#. This question is frequently asked in interviews, so make sure to revise it before heading for your interview.