QFE

Quick Fix Engineering (QFE)

Quick Fix Engineering (QFE) is a Microsoft term for the delivery of individual service updates to its operating systems and application programs. Using WMIC command, QFE feature can give lot of information for patch management.

Need to know if patching was successful or failed?

This might happen that after server reboot, you find everything is up and running but the patch installation was failed. To check if patch is successfully installed or failed, we can use “wmic” command. These are explained below:

wmic qfe list

This command will give you the list of all the patch details which was installed in the machine. Here the list generated will be little bit complicated. In that case we can use another command to format the output in command prompt.

wmic qfe list /format: list

This command will generate the same list above in a proper and understandable format. Using this command you can find the patch or hotfix name and date of installation.

wmic qfe list brief /format:htable > patchdetail.html

If we need the list of all patch and their details in a proper format, not in command prompt, then we can use the below command to generate the output in an appropriate format.

Need to know the server reboot time?

To check whether server was rebooted after the patch installation, it is required to verify the last reboot time of the server. This reboot time helps us to check whether patching has been done or not.

We can find the reboot time using the below commands:

Net statistics workstation

This command will give you the last reboot time of the server.

 

Systeminfo

This is another alternative command to find the last reboot time of the system. This command will generate a huge detailed report about the system, that is why I prefer using Net command to get the output in short.

JOINS

JOINS

--============================================[JOINS]========================================================================

--JOIN clause is used whenever we have to select data from 2 or more tables.

--Our two table are below---

SELECT * FROM Person.Contact [19972 rows]

Select * From Sales.Individual [18484 rows]

--Joining without using JOIN Clause (Only Matched)[18484 rows]---------

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact,Sales.Individual

WHERE Person.Contact.ContactID = Sales.Individual.ContactID

--JOIN (By default it will be INNER JOIN)(Only Matched)[18484 rows]

--The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on.

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact JOIN Sales.Individual

ON Person.Contact.ContactID = Sales.Individual.ContactID

--LEFT OUTER JOIN (simply LEFT JOIN) (19972 rows)---

-- selects all the rows from the first table listed after the FROM clause, no matter if

--they have matches in the second table.

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact LEFT OUTER JOIN Sales.Individual

ON Person.Contact.ContactID = Sales.Individual.ContactID

--RIGHT OUTER JOIN (simply RIGHT JOIN) (18484 rows)

-- selects all the rows from the second table listed after the FROM clause

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact RIGHT OUTER JOIN Sales.Individual

ON Person.Contact.ContactID = Sales.Individual.ContactID

HAVING

HAVING

--===========================================[HAVING]=======================================================================

--HAVING clause is used to restrict conditionally the output of a SQL statement,

--by a SQL aggregate function used in your SELECT list of columns.

--This will throw Error because we can't specify criteria in a SQL WHERE clause against a column in the SELECT list for which SQL aggregate function is used

SELECT Title, SUM(EmailPromotion) AS EmailPromoGroup

FROM Person.Contact

Where UM(EmailPromotion)> 100

Group By Title

---Having------

SELECT Title, SUM(EmailPromotion) AS EmailPromoGroup

FROM Person.Contact

Group By Title

HAVING SUM(EmailPromotion)> 100

GROUP BY

GROUP BY

--==========================================[GROUP BY]======================================================================

--GROUP BY statement is used along with the SQL aggregate functions like SUM to provide

--means of grouping the result dataset by certain database table column(s).

--Without Group By----

SELECT SUM(EmailPromotion) AS EmailPromoGroup

FROM Person.Contact

--With Group By But No Correspoding Grouping----

SELECT SUM(EmailPromotion) AS EmailPromoGroup

FROM Person.Contact

Group By Title

---With Group By and With additional column of the Group(Title)----

SELECT Title, SUM(EmailPromotion) AS EmailPromoGroup

FROM Person.Contact

Group By Title

Average & Sum

AVG & SUM

--==========================================[AVG]===========================================================================

--AVG aggregate function selects the average value for certain table column.

--Avg---------

SELECT AVG(EmailPromotion) AS AvgEmailPromotion

FROM Person.Contact

--==========================================[SUM]===========================================================================

--SUM aggregate function allows selecting the total for a numeric column.

---Sum--------

SELECT SUM(EmailPromotion)

FROM Person.Contact

MAX & MIN

MAX & MIN

--==========================================[MAX and MIN]===================================================================

-- MAX aggregate function allows us to select the highest (maximum) value for a certain column.

--Max---------

SELECT MAX(EmailPromotion) AS MaxEmailPromo

FROM Person.Contact

--Min---------

SELECT MIN(EmailPromotion) AS MinEmailPromo

FROM Person.Contact

COUNT

COUNT

--========================================== [COUNT]=======================================================================

--COUNT aggregate function is used to count the number of rows in a database table.

--Count--

SELECT COUNT(LastName) AS NumberOfContacts

FROM Person.Contact

OR & AND

OR & AND

--============================================[OR and AND]=================================================================

--AND clause is used when you want to specify more than one condition in your SQL WHERE clause

--and at the same time you want all conditions to be true.

---AND----

SELECT * FROM Person.Contact

WHERE LastName = 'Achong'

AND

ContactID = '1'

---OR-----

--Using OR clause, we can display all the results which satisfies either of the condition

SELECT * FROM Person.Contact

WHERE LastName = 'Achong'

OR

ContactID <= '10'

--- AND + OR ------

--You can combine AND and OR clauses anyway you want and you can use parentheses to define your logical expressions.

SELECT * FROM Person.Contact

WHERE (FirstName = 'Gustavo' OR FirstName = 'Catherine') AND LastName = 'Achong'

ORDER BY

ORDER BY

--=============================================[ORDER BY]==================================================================

--Order By is used to sort SQL result sets by some columns

--Order By (default Accending)

SELECT * FROM Person.Contact

ORDER BY ContactID

--Order By (Decending order)

SELECT * FROM Person.Contact

ORDER BY ContactID DESC

--Order By (Ascending order)

SELECT * FROM Person.Contact

ORDER BY ContactID ASC

DELETE

DELETE

--=============================================[DELETE]==================================================================

--Delete statement is used to remove data from the table

--Delete specific data from a table

DELETE FROM Person.Contact

WHERE FirstName = 'Habib'

--Delete Entire data from the Table

DELETE FROM Table1

UPDATE

UPDATE

--==============================================[ UPDATE ]=================================================================

--The SQL UPDATE clause changes the data in already existing database row(s) and usually

--we need to add a conditional SQL WHERE clause to our SQL UPDATE statement in order to

--specify which row(s) we intend to update.

--Update table column value (specify the row using Where clause)--

UPDATE Person.Contact

SET Phone = 9550000520, EmailAddress = 'habib.ccna@gmail.com'

WHERE FirstName = 'Habib'

SELECT * FROM Person.Contact

WHERE LastName Like 'sheik%'

WHERE

WHERE

--==============================================[ WHERE ]=====================================================================

--The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query.

--Where (equal to)---

SELECT * FROM Person.Contact

WHERE LastName='Achong'

--Where (Not equal <>)--

SELECT * FROM Person.Contact

WHERE LastName <> 'Achong'

--Where (Greater than >)---

SELECT * FROM Person.Contact

WHERE ContactID > 5

--Where (Greater or equal >= )---

SELECT * FROM Person.Contact

WHERE ContactID >= 5

--Where (Less than <)---

SELECT * FROM Person.Contact

WHERE ContactID < 5

--Where (Less or equal <=)

SELECT * FROM Person.Contact

WHERE ContactID <= 5

--Where Like ----

SELECT * FROM Person.Contact

WHERE LastName Like 'Ach%'

SELECT * FROM Person.Contact

WHERE Phone Like '398%'

            -- Wildcard Concept: first digit of the number is between 3-6, second digit can be any number _, third digit should be 4 and rest cab be any number

SELECT * FROM Person.Contact

WHERE Phone LIKE '[3-6]_4%'

--Where Between--

SELECT * FROM Person.Contact

WHERE ContactID BETWEEN '1' AND '50'

--Where In-----

SELECT * FROM Person.Contact

WHERE ContactID IN ('1', '15')

DISTINCT

DISTINCT

--==============================================[ DISTINCT ]====================================================================

--The SQL DISTINCT clause is used together with the SQL SELECT keyword, to return

--a dataset with unique entries for certain database table column.

--Distinct (to see different surnames in lastname column)

SELECT DISTINCT LastName

FROM Person.Contact

SELECT INTO

SELECT INTO

--==============================================[SELECT INTO]===============================================================

--The SQL SELECT INTO statement is used to select data from a SQL database

--table and to insert it to a different table at the same time.

--Select Into table(copy entire data)---

SELECT *

INTO table2

FROM table1

--Select Into (copies selected columns)--

SELECT Column1, Column2, Column3,

INTO Table2

FROM Table1

SELECT

SELECT

--=========================================[SELECT]=====================================================================

--The SQL SELECT statement is used to select data from a SQL database table.

--This is usually the very first SQL command every SQL newbie learns and this

--is because the SELECT SQL statement is one of the most used SQL commands.

--Select table--

SELECT * FROM Person.Contact

--Select Coloumns of table--

SELECT FirstName,MiddleName FROM Person.Contact

INSERT INTO

INSERT INTO

--=========================[ INSERT INTO ]===================================================

--This is used to add a new row into the database table.

--Insert Into Table (Use AdventureWorks Database)--

INSERT INTO Person.Contact

VALUES ('2998', '0','Mr.','Habibar','Rahman','Sheikh'.....)

--Insert Into Table (in specified column)

INSERT INTO Person.Contact (FirstName, LastName,Phone,EmailAddress,PasswordHash,PasswordSalt)

VALUES ('Habib', 'Sheikh', '9985476188', 'habib.mvp@gmail.com','jhshdhsjkddujiushdfijmujsgudh','uyuhjs=')

SELECT * FROM Person.Contact

WHERE LastName Like 'sheik%'

Renaming

sp_rename

--===============[sp_rename==================================================================

-- Renaming Table

-- System procedure sp_rename modifies the name of an existing table

 sp_rename employee, customer

 SELECT * FROM customer

DROP

DROP

--=========================================[DROP]=======================================================================

--To delete the entire table with the date, Drop is used.

--NOTE: here employee table is referenced by foreign key of department table

--so, we need to drop department table first or drop Fkey first then drop employee table.

DROP TABLE department

DROP TABLE employee

TRUNCATE

TRUNCATE

--=========================================[TRUNCATE]===================================================================
--Truncate is used when we want to delete the data inside the table, and not the table itselfTRUNCATE TABLE employee

ALTER

ALTER

--===================[ALTER]============================================================

--ALTER is used to add or delete new columns to the existing table or to change the datatypes

-- Adds the column telephone_no to the employee table.

ALTER TABLE employee

ADD telephone_no CHAR(12) NULL

SELECT * FROM employee

--chnage the datatype of the column telephone_no

ALTER TABLE employee

ADD telephone_no NVARCHAR(15)

SELECT * FROM employee

--deletes the column telephone_no

ALTER TABLE employee

DROP telephone_no

SELECT * FROM employee

IDENTITY

IDENTITY

--=========================================[IDENTITY]==================================================================

--This is used when we would like the value of the primary key field to be created automatically

--every time a new record is inserted.

--Identity---

CREATE TABLE employee(

     ID          int PRIMARY KEY IDENTITY,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

SQL Constraints

CONSTRAINTS

--=========================================[SQL CONSTRAINTS]===========================================================

--Constraints are used to limit the type of data that can go into a table.

--We will focus on NOT NULL, UNIQUE,PRIMARY KEY ,FOREIGN KEY,CHECK ,DEFAULT constraints-----

--NOT NULL-------

CREATE TABLE employee(

     ID          int NOT NULL,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--UNIQUE---------

CREATE TABLE employee(

     ID          int NOT NULL UNIQUE,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--PRIMARY KEY-----

CREATE TABLE employee(

     ID          int NOT NULL PRIMARY KEY,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--FOREIGN KEY-----

--here we have two tables employee and department

CREATE TABLE employee(

     ID          int NOT NULL PRIMARY KEY,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

CREATE TABLE department(

     DepartmentID int NOT NULL PRIMARY KEY,

     name        nvarchar (10),

     designation nvarchar (10),

     empID          int references employee(ID))

--CHECK-----------

CREATE TABLE employee(

     ID          int NOT NULL CHECK (ID>0),

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10),

     region      char (1))

--DEFAULT (see below example)--

--=========================================[DEFAULT]====================================================================

--To specify a default value, add "Default [value]" after the data type declaration

--Default City Unknown

CREATE TABLE employee(

     ID          int,

     name        nvarchar (10),

     salary      int,

     start_date  datetime,

     city        nvarchar (10)default 'Unknown',

     region      char (1))

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (3,  'Habib', 90020,  '12/03/99',DEFAULT,  'W')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (4,  'Linda', 40620,  '11/04/97', 'New York', 'N')

 INSERT INTO employee (ID, name,    salary, start_date, city,       region)

               values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')

SELECT * FROM employee