Oracle SQL Fundamental 1 - Practice Exercise 2

1. select last_name, salary from employees where salary > 12000
2. select last_name, department_id from employees where employee_id=176
3. select last_name, salary from employees where salary not between 5000 and 12000
4. select last_name,job_id,hire_date from employees where last_name in ('Matos', 'Taylor')
5. select last_name, department_id from employees where department_id in (20,50)
6. select last_name, salary from employees where (salary between 5000 and 12000) AND DEPARTMENT_ID in (20,50)
7. select last_name, hire_date from employees where hire_date >='01-JAN-1994' and hire_date<='31-Dec-1994'
8. select last_name, hire_date from employees where manager_id is null
9. select last_name, salary, COMMISSION_PCT from employees where commission_pct is not null
10. select last_name, salary from employees where salary>&salary_amount
11. select employee_id,last_name,salary,department_id from employees where manager_id =&ID_OF_MANAGER order by &ORDER_BY_COLUMN
12. select last_name from employees where last_name like '__a%'
13. select last_name from employees where last_name like '%a%e%' or last_name like '%e%a%'
14. select last_name,job_id, salary from employees where job_id in ('SA_REP','ST_CLERK')  and 17. salary not in (2500,3500,7000)
15. select last_name, salary, commission_pct, salary * commission_pct "Commission" from employees where commission_pct=.2

Oracle SQL Fundamental 1 - Practice Exercise 1

List of questions and answers to practice exercises given in Oracle Course Material.

Exercise - 1
1. The following SELECT statement executes successfully.
select last_name, job_id, salary as Sal from employees;
Ans: TRUE

2. The following SELECT statement executes successfully.
select * from job_grades;
Ans: TRUE

3. There are 4 coding errors in the following SQL statement can you identify them ?

select employee_id, last_name sal x 12  ANNUAL SALARY from employees
Errors.
1. Comma after last_name
2. Comma after sal x 12
3. Opening double quote before Annual salary
4. Ending double quote after Annual salary.

Questions 4,5,6 cannot be answered, it just has to be understood.

Question 7.
select employee_id , last_name, job_id,hire_date as "Start Date" from employees

Question 9
select distinct job_id from employees;

Q 10
select employee_id "Emp #",last_name Employee,job_id Job, hire_date as "HireDate" from employees

Q11 - Two possible answers I could think.
1. select last_name||' , '||job_id "Employee and Title" from employees

2. select concat(last_name,concat(' , ',job_id)) "Employee and Title" from employees

Question 12
select employee_id||','||first_name||','||last_name||','||email||','||phone_number||','||hire_date||','||job_id||','||salary||','||commission_pct||','||manager_id||','||department_id "The Output" from employees

Utilities for use with MFC

Get File Extension from an URL stored in CString

//COMMENT: This method takes an URL ( eg: http://www.abc-website.com/images/background.gif) as an argument and returns .gif as an extension.

CString getFileExtension(CString fileName)
{
CString ch=L".";
int totalLength=fileName.GetLength();
int idxOfDot=fileName.Find(ch,totalLength-5);
CString fileExtn=fileName.Mid(idxOfDot,(totalLength-idxOfDot)); // totalLength-1 because index is zero based.
return fileExtn;
}

Swap two entries in a std::vector

// COMMENT: The following code snippet swaps two rows in a std::vector
// Each entry in this vector is a pointer to an UrlData object.
void swapRows(int swapFrom, int swapTo)
{
     vector<UrlData *>::iterator itr=getUrlVector()->begin();
     if(swapTo > swapFrom)
    {
          UrlData* rowRemoved=getUrlVector()->at(swapTo);
getUrlVector()->erase(itr+swapTo);
getUrlVector()->insert(itr+swapFrom,rowRemoved);
     }
     else
     {
          UrlData* rowRemoved=getUrlVector()->at(swapFrom);
          getUrlVector()->erase(itr+swapFrom);
          getUrlVector()->insert(itr+swapTo,rowRemoved);
     }
}

Windows Form with Rounded Border

The Windows Form is one of the basic entities using which we interact with the Windows GUI Operating System.  A window or a dialog that we see as part of Windows GUI is a System.Widows.Forms.Form control.  When a window is created, it is created with all basic components like a title bar, three set of buttons [ Minimize,Maximize,Close]  in the top right corner which help us to minimize, maximize and close forms.  Along with the list of  components given above, it also has a rectangular area called the Client Area.  The Client Area is the rectangular area in which we can drop controls and create the GUI based on our needs.  Those of you who are interested to go behind the scenes and find out what is happening, will be benefitted by this blog. I will be touching the following areas. 

1. C# .NET API's
2. Windows Runtime Interop Services.

In this short tutorial, we will be concentrating on the following

1. How to create a borderless Form ?
2. How to create a Form with a non-rectangular border ?
3. How to make the Form created in Question 2, look like a default Windows with System Menu ?

How to create a borderless form ?

Borderless forms might not be required for normal application developers who use basic controls.  But it will be a basic requirement for those users who require a more advanced contemporary look and feel.  Non traditional Windows Application Users might need this kind of borderless forms.  Most of the time users will add more to a borderless form and make it look like a Form with non traditional 3D border.  This borderless form can be created with just one line of code.  It is created by setting the FormBorderStyle property of the System.Windows.Forms.Form.   This property in most of the circumstances be set in the <FormName>.Designer.cs file.   For e.g., if the form name is "Form1", then the following step will create a Windows Form without border.  Here "this" variable refers to the System.Windows.Forms.Form control

this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None;

or

<Form1>.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None;

where <Form1> is the name of your System.Windows.Forms.Form.

I believe this is a very basic step.  If you add any one the lines of code appropriately you will be able to see a borderless window.

How to create a Form with a rounded (non-rectangular) border ?

The answer to the above question is the result of a series of steps.  I have given a picture below to illustrate. 

The above is the sample of a Form with a non-rectangular Window. To do this we have to set the FormBorderStyle property of the Form as given above. Then we have to paint the window as given below.

The border of this window can be separated into 8 sections.  4 rounded borders and 4 lines connecting these rounded borders.


The 4 border lines that connect them are

1. Top border
2. Left border
3. Right border
4. Bottom border


The 4 rounded Borders are shown as quadrants of a circle.


1. TopLeft rounded Corner - 2nd quadrant
2. TopRight  rounded  Corner - 1st quadrant
3. BottomLeft rounded Corner - 3rd quadrant
4. BottomRight rounded Corner - 4th quadrant

To draw a rounded corner we need to take a circle and divide it into 4 quadrants.  Illustrated using a picture below.

We have to draw each of the arcs of these quadrants as the corners and create the rounded border for the window



We have to create a GraphicsPath (System.Drawing.Drawing2D.GraphicsPath) ( which connects all the above 8 parts. In the following order.

1. Top Left Corner
2. Left Border
3. BottomLeft Corner
4. Bottom Corner
5. BottomRight Corner
6. Right Corner
7. Top Right Corner
8. Top Corner

This is illustrated using the code given below.

public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        int borderRadius = 30;
        protected override void OnPaint(System.Windows.Forms.PaintEventArgs e)
        {
            base.OnPaint(e);
            Graphics g = e.Graphics;
            Pen p = new Pen(Color.Black);
            int height = Height;
            int width = Width;
            // 4 Border Lines
            // x1,y1 -----> x2 y2 Left Border Line
            // x3y3  -----> x4,y4 Bottom Border Line
            // x5,y5 -----> x6,y6 Right Border Line
            // x7,y7 -----> x8,y8 Top Border Line

            // x1,y1 ( Left Top), x2,y2 ( Left Bottom of Left Border), x3,y3 (Left Bottom of Bottom Border),  x4,y4 (Right Bottom of Bottom Border)
            int x1 = 0, y1 = 0, x2 = 0, y2 = Height, x3 = 0, y3 = Height, x4 = Width, y4 = Height;
            // x5,y5 ( Bottom Right) x6,y6 (Top Right) x7,y7 Right Top
            int x5 = Width, y5 = Height, x6 = Width, y6 = 0, x7 = Width, y7 = 0, x8 = 0, y8 = 0;
            System.Drawing.Drawing2D.GraphicsPath gp = new System.Drawing.Drawing2D.GraphicsPath();
            y1 = borderRadius / 2;
            x8 = borderRadius / 2;
            y2 = height - (borderRadius / 2);
            x3 = borderRadius / 2;
            x4 = Width - (borderRadius / 2);
            y5 = Height - (borderRadius / 2);
            y6 = borderRadius / 2;
            x7 = Width - (borderRadius / 2);
            // Top Left Arc
            gp.AddArc(new Rectangle(0, 0, borderRadius, borderRadius), 180, 90);
            // Left Border
            gp.AddLine(new Point(x1, y1), new Point(x2, y2));
            // Bottom Left Arc
            gp.AddArc(new Rectangle(0, height - borderRadius, borderRadius, borderRadius), 90, 90);
            // Bottom Line
            gp.AddLine(new Point(x3, y3), new Point(x4, y4));
            // Bottom Right Arc
            gp.AddArc(new Rectangle(width - borderRadius, height - borderRadius, borderRadius, borderRadius), 0, 90);
            // Right Border
            gp.AddLine(new Point(x5, y5), new Point(x6, y6));
            // Top Right Border
            gp.AddArc(width - borderRadius, 0, borderRadius, borderRadius, 270, 90);
            // Top Border
            gp.AddLine(new Point(x7, y7), new Point(x8, y8));
            gp.CloseFigure();
            this.Region = new Region(gp);
        }
    }

The output of the above is code is the screen shot given below.