Saturday, November 7, 2015

Oracle SQL: Reporting Aggregated Data Using the Group Functions

Types of Group Functions

  1. AVG 
  2. COUNT 
  3. MAX
  4. MIN 
  5. STDDEV 
  6. SUM
  7. VARIANCE

Guidelines for Using Group Functions

  • DISTINCT makes the function consider only nonduplicate values; ALL makes it consider every value, including duplicates. The default is ALL and therefore does not need to be specified.
  • The data types for the functions with an expr argument may be CHAR, VARCHAR2, NUMBER, or DATE. 
  • All group functions ignore null values. To substitute a value for null values, use the NVL, NVL2, or COALESCE functions.

Group Functions: Syntax
SELECT     [column,] group_function(column), ...
FROM  table
[WHERE  condition]
[GROUP BY  column]
[ORDER BY  column];

Using the Group Functions
You can use AVG, SUM, MIN, and MAX functions against columns that can store numeric data. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives.

                                             <<Back                 Next>>

Oracle SQL: SELECT (Literal Character Strings)

Literal Character Strings
>> A literal is a character, a number, or a date that is included in the SELECT statement.
>> Date and character literal values must be enclosed by single quotation marks.

>> Each character string is output once for each row returned.

Example:
SELECT customer_name||'is a'||cust_id AS "Customer Details" FROM   customer;


                  <<Back       Next>>

Oracle SQL: SELECT (Arithmetic Expressions)


                       
Operator
Description
+
 Add
-
 Subtract
*
 Multiply
/
 Divide
  

Using Arithmetic Operators: 
SELECT customer_name,unit, price, price+ 300 FROM  customers;

Operator Precedence
If an arithmetic expression contains more than one operator, multiplication and division are evaluated first. If operators in an expression are of the same priority, then evaluation is done from left to right. You can use parentheses to force the expression that is enclosed by parentheses to be evaluated first.

Rules of Precedence:
1. Multiplication and division occur before addition and subtraction.
2. Operators of the same priority are evaluated from left to right.
3. Parentheses are used to override the default precedence or to clarify the statement.

                                            <<Back                           Next>>

Oracle SQL: SELECT

To extract data from the database, you need to use the structured query language (SQL) SELECT statement.
A SELECT statement retrieves information from the database. With a SELECT statement, you can use the following capabilities:
  1. Projection: Choose the columns in a table that are returned by a query. Choose as few or as many of the columns as needed
  2. Selection: Choose the rows in a table that are returned by a query. Various criteria can be used to restrict the rows that are retrieved. 
  3. Joining: Bring together data that is stored in different tables by specifying the link between them. SQL joins are covered in more detail in a later lesson.
Basic SELECT Statement:
In its simplest form, a SELECT statement must include the following:
A SELECT clause, which specifies the columns to be displayed
A FROM clause, which identifies the table containing the columns that are listed in the SELECT clause
In the syntax: 
SELECT *|{[DISTINCT] column|expression [alias],...} FROM    table;

Note:
  • SELECT identifies the columns to be displayed
  • FROM identifies the table containing those columns
SELECT is a list of one or more columns 
*   selects all columns
DISTINCT suppresses duplicates
column|expression selects the named column or the expression
alias         gives selected columns different headings
FROM table specifies the table containing the columns
A clause is a part of a SQL statement. For example, SELECT net_id, last_name, ... is a clause.
A statement is a combination of two or more clauses. For example, SELECT * FROM TABLE is a SQL statement.
Writing SQL Statements:
Using the following simple rules and guidelines, you can construct valid statements that are both easy to read and easy to edit:
SQL statements are not case-sensitive (unless indicated).
SQL statements can be entered on one or many lines. 
Keywords cannot be split across lines or abbreviated.
Clauses are usually placed on separate lines for readability and ease of editing.
Indents should be used to make code more readable.
Keywords typically are entered in uppercase; all other words, such as table names and columns, are entered in lowercase.
For example, the following SQL statement (like the example in the slide) displays all columns and all rows of the PEOPLES table:
SELECT  people_id, country_name, location_id FROM    peoples;

 <<Back                        Next >>          

More Useful Software Free Download.


Installing SQL Server 2000


To Install SQL Server 2000 Basic Local Installation
1.       Insert the Microsoft SQL Server 2000 compact disc in your CD-ROM drive (if the compact disc does not run automatically, double-click Autorun.exe in the root directory of the compact disc), select SQL Server 2000 Components, and then select Install Database Server. Setup prepares the SQL Server Installation Wizard. At the Welcome page, clickNext.
2.       In the Computer Name dialog box, Local Computer is the default option, and the local computer name appears in the text box. Click Next.
3.       In the Installation Selection dialog box, click Create a new instance of SQL Server, or install Client Tools, and then click Next. Follow directions on the User Information, Software License Agreement and related pages. In theInstallation Definition dialog box, click Server and Client Tools, and then click Next.
4.       In the Instance Name dialog box, if the Default check box is available, you can install either the default or a named instance. If the Default check box is not available, a default instance has already been installed, and you can install only a named instance.
o    To install the default instance, click to select the Default check box, and then click Next.
o    To install a named instance, click to clear the Default check box, type a new named instance in the Instance Name box, and then click Next.
5.       In the Setup Type dialog box, click Typical or Minimum, and then click Next.
6.       In the Service Accounts dialog box, accept the default settings, type your domain password, and then click Next. In theAuthentication Mode dialog box, accept the default setting, and then click Next. When you finish specifying options, click Next in the Start Copying Files dialog box.
7.       In the Choose Licensing Mode dialog box, make selections according to your license agreement, and then clickContinue to begin the installation. In the Setup Complete dialog box, click Yes, I want to restart my computer now, and then click Finish.
To Install Client Tools Only for SQL Server 2000
1.       Insert the Microsoft SQL Server 2000 compact disc in your CD-ROM drive (if the compact disc does not run automatically, double-click Autorun.exe in the root directory of the compact disc), select SQL Server 2000 Components, select Install Database Server, and then click Next at the Welcome page of the SQL Server Installation Wizard.
2.       In Computer Name dialog box, Local Computer is the default option, and the local computer name appears in the edit box. Click Next.
3.       In the Installation Selection dialog box, click Create a new instance of SQL Server, or install Client Tools, and then click Next.
4.       Follow the directions on the User Information, Software License Agreement, and related pages.
5.       In the Installation Definition dialog box, click Client tools only, and then click Next.
6.       In the Select Components dialog box, accept the defaults or select the components you want, and then click Next. You can select an item in the Components list, such as Management Tools, and then select items from the related Sub-Components list, such as Enterprise Manager. Click to select items that you want to install, and click to clear the check box for the items you do not want to install. For information about each component, select the item, and view theDescription box.
7.       In the Start Copying Files dialog box, click Next to complete the installation of the client tools.
 To Install Connectivity Only for SQL Server 2000
1.       Insert the Microsoft SQL Server 2000 compact disc into your CD-ROM drive (if the compact disc does not run automatically, double-click Autorun.exe in the root directory of the compact disc), and then select SQL Server 2000 Components.
2.       Select Install Database Server. Setup prepares the SQL Server Installation Wizard. At the Welcome page, click Next.
3.       In the Computer Name dialog box, Local Computer is the default option, and the local computer name appears in the text box. Click Next.
4.       In the Installation Selection dialog box, click Create a new instance of SQL Server, or install Client Tools, and then click Next.
5.       Follow the directions on the User Information, Software License Agreement and related pages.
6.       In the Installation Definition dialog box, click Connectivity Only, and then click Next.
7.       In the Start Copying Files dialog box, click Next to complete the installation.




Free and open-source VLC media player

VLC media player
VLC media player (commonly known as VLC) is a portable, free and open-source, cross-platform media player and streaming media server. Simple, fast and powerful media player. Plays everything: Files, Discs, Webcams, Devices and Streams.
Plays most codecs with no codec packs needed: MPEG-2, DivX, H.264, MKV, WebM, WMV, MP3...

VLC runs on all platforms: Windows, Linux, Mac OS X, Unix...

VLC is completely free, no spyware, no ads and no user tracking. It Can do media conversion and streaming. To get VLC click here.



Download More Free Software. Download More Free Software. 

Friday, November 6, 2015

7-Zip is a open source file archiver software

7-Zip
7-Zip is a open source file archiver software. This software is very useful. This software run on Microsoft Windows, GNU/Linux, Mac OS X. A 32-bit and 64-bit version are available. 7-Zip supports a number of other compression and non-compression archive formats (both for packing and unpacking) including 7z, ZIP, Gzip, bzip2, xz, tar and WIM. The utility also supports unpacking APM, ARJ, CHM, cpio, DEB, FLV, JAR, LHA/LZH, LZMA, MSLZ, Office Open XML, onepkg, RAR, RPM, smzip, SWF, XAR and Z archives and CramFS, DMG, FAT, HFS, ISO, MBR, NTFS, SquashFS, UDF and VHD disk images. 7-Zip supports the ZIPX format for unpacking only. To download 7-Zip software click here.


Download More Free Software.